XML Bulk Load

EDN Admin

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
Hi,
I need to import an XML file into a SQL Server table (mapping certain elements and attributes to certain columns). After a bit of research I have decided to use the SQLXMLBulkload class, available within SQLXML 4.0.
I have used Visual Studio to create my Schema file, and have a Schema as follows:

<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; <?<span style="color:#A31515; xml <span style="color:Red; version<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; 1.0<span style="color:Black; " <span style="color:Red; encoding<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; utf-8<span style="color:Black; "<span style="color:Blue; ?>
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; schema <span style="color:Red; xmlns:xsi<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; http://www.w3.org/2001/XMLSchema-instance<span style="color:Black; " <span style="color:Red; attributeFormDefault<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; unqualified<span style="color:Black; " <span style="color:Red; elementFormDefault<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; qualified<span style="color:Black; " <span style="color:Red; xmlns:xs<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; http://www.w3.org/2001/XMLSchema<span style="color:Black; " <span style="color:Red; xmlns:sql<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; urn:schemas-microsoft-com:mapping-schema<span style="color:Black; "<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element <span style="color:Red; name<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; merchandiser<span style="color:Black; "<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; complexType<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; sequence<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element <span style="color:Red; name<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; header<span style="color:Black; "<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; complexType<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; sequence<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element <span style="color:Red; name<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; merchantId<span style="color:Black; " <span style="color:Red; type<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; xs:unsignedShort<span style="color:Black; " <span style="color:Blue; />
<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; sequence<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; complexType<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element <span style="color:Red; maxOccurs<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; unbounded<span style="color:Black; " <span style="color:Red; name<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; book<span style="color:Black; " <span style="color:Red; sql:relation<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; MyTable<span style="color:Black; "<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; complexType<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; sequence<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element <span style="color:Red; name<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; author<span style="color:Black; "<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; complexType<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; sequence<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element <span style="color:Red; name<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; name<span style="color:Black; " <span style="color:Red; type<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; xs:string<span style="color:Black; " <span style="color:Blue; />
<span style="color:Blue; <<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element <span style="color:Red; name<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; surname<span style="color:Black; " <span style="color:Red; type<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; xs:string<span style="color:Black; " <span style="color:Blue; />
<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; sequence<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; complexType<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element<span style="color:Blue; >

<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; sequence<span style="color:Blue; >

<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; complexType<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element<span style="color:Blue; >

<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; sequence<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; complexType<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; element<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; xs<span style="color:Blue; :<span style="color:#A31515; schema<span style="color:Blue; >

[/code]

An example of my XML looks like:
<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; <?<span style="color:#A31515; xml <span style="color:Red; version<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; 1.0<span style="color:Black; " <span style="color:Red; encoding<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; UTF-8<span style="color:Black; " <span style="color:Blue; ?>
<span style="color:Blue; <<span style="color:#A31515; merchandiser <span style="color:Red; xmlns:xsi<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; http://www.w3.org/2001/XMLSchema-instance<span style="color:Black; " <span style="color:Red; xsi:noNamespaceSchemaLocation<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; merchandiser.xsd<span style="color:Black; "<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; header<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; merchantId<span style="color:Blue; >35725<span style="color:Blue; </<span style="color:#A31515; merchantId<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; header<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; book <span style="color:Red; id<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; 29902<span style="color:Black; " <span style="color:Red; name<span style="color:Blue; =<span style="color:Black; "<span style="color:Blue; The Mill on the Floss<span style="color:Black; "<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; author<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; surname<span style="color:Blue; >Hardy<span style="color:Blue; </<span style="color:#A31515; surname<span style="color:Blue; >
<span style="color:Blue; <<span style="color:#A31515; name<span style="color:Blue; >Thomas<span style="color:Blue; </<span style="color:#A31515; name<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; author<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; book<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; header<span style="color:Blue; >
<span style="color:Blue; </<span style="color:#A31515; merchandiser<span style="color:Blue; >
[/code]
Notice that I have only added sql::relation=myTable within the element book. This is because I only wish to import data from within the element book.
When I try to import the XML, I get the error message: Schema: relationship expected on header.

However, there is no relationship with header - I do not wish to use this element. If I remove the header element from the XML, I get:
Schema: relationship expected on book. I am confused, there is no other tables in the database for it to be related to.
I guess I am misunderstanding how the Schema needs to be marked-up for the SQLXMLBulkload class.
Very grateful for any help!
Martin


View the full article
 

Similar threads

D
Display Issue
Replies
0
Views
104
Donald Uko
D
D
Icon Bar is not working again
Replies
0
Views
73
Donald Uko
D
D
Really need your help
Replies
0
Views
103
Donald Uko
D
M
Error downloading metadata from the address when adding web service reference
Replies
0
Views
1,190
Mike Lusicic
M
S
System.Xml.Linq.XContainer.Element(…) returned null
Replies
0
Views
200
salimi.NET
S
Back
Top