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?

Can anyone ??!!

See if the use of complexType in the following examples will you:
http://blogs.msdn.com/monicafrintu/a...lbulkload.aspx
http://msdn2.microsoft.com/en-us/library/aa225754.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com