XML Bulk Load with nested elements and attributes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

XML Bulk Load with nested elements and attributes

Author

Topic

DennBen78
Starting Member

1 Post

Posted - 2008-03-25 : 12:40:11

Hi, I'm using SQL 2005 and am trying to use the XML Bulk Load COM
object to import XML data into a table. I'm having difficulty mapping
schema file. In the MS SQL Documentation I found an example using this
XML format:
<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>

and how the above format would translate to this schema:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="CustomerId" dt:type="int" />
<ElementType name="CompanyName" dt:type="string" />
<ElementType name="City" dt:type="string" />

<ElementType name="ROOT" sql:is-constant="1">
<element type="Customers" />
</ElementType>

<ElementType name="Customers" sql:relation="Customer">
<element type="CustomerId" sql:field="CustomerId" />
<element type="CompanyName" sql:field="CompanyName" />
<element type="City" sql:field="City" />
</ElementType>

</Schema>

That's nice and all, but my XML file isn't so simple. I've got nested
elements in it, and this doesn't show me how to map nested elements in
a schema file.

For example if my XML file was this:
<ROOT>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<Demographics>
<CityInfo State="CA" zip="11222">LA</CityInfo>
</Customers>

</ROOT>

How would I create a schema file to map to that?

Any help would be greatly appreciated!

Thanks in advance,
DB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources