Example of XSD for a simple table to load into SQL Server using XML Bulk Load

Question

I am trying to get a relatively simple xml file to load into SQL Server 2008 as a table using bulk load. My primary issue is crafting a XSD that works with bulk load. Here is my current XSD. The table I am looking to create will only have nvarchar fields and datetime fields.

I know I have errors in how I put together the XSD. Now the XSD is working but bulk load is not loading any data.

I am really looking for an example of a XSD and XML file that can be successfully loaded into SQL Server using Bulk Load. That allows me to specify the max length and data type. The XML will include some non standard characters so I have used for that data.

Below are a sample of my XSD and XML. Thanks.

<?xml version="1.0" ?>
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:element name="FWO2-2011-03-03T22-53-47" sql:relation="XMLTESTTBL2">
<xsd:complexType>
<xsd:attribute name="fIld" type="sqltypes:int" use="required"/>
<xsd:attribute name="o">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="255"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="n">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="255"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="f">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="255"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="s" type="sqltypes:int"/>
<xsd:attribute name="a" type="sqltypes:int"/>
<xsd:attribute name="l" type="sqltypes:datetime"/>
<xsd:attribute name="d" type="sqltypes:datetime"/>
<xsd:attribute name="e">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="255"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="b" type="sqltypes:datetime"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>

<?xml version="1.0" ?>
<dataroot xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="FWO2-2011-03-03T22-53-47.xsd" generated="2008-05-08T16:37:19">
<FWO2-2011-03-03T22-53-47>
    <n><![CDATA[2007 Protiviti Pricing Tool.zip]]></n>
    <s>3073536</s>
    <f><![CDATA[C:\Users\ethkin01\Desktop\Desktop\2011-02-06]]></f>
    <a>32</a>
    <l>1/28/2011 3:27:43 PM</l>
    <d>1/12/2011 12:09:31 PM</d>
    <e><![CDATA[.zip]]></e>
    <b>1/12/2011 12:09:31 PM</b>
</FWO2-2011-03-03T22-53-47>
<FWO2-2011-03-03T22-53-47>
    <n><![CDATA[2010 Updated 1-11-11.xlsm]]></n>
    <s>1149607</s>
    <f><![CDATA[C:\Users\ethkin01\Desktop\Desktop\2011-02-06]]></f>
    <a>32</a>
    <l>1/11/2011 4:59:46 PM</l>
    <d>1/11/2011 4:59:45 PM</d>
    <e><![CDATA[.xlsm]]></e>
    <b>1/11/2011 4:59:45 PM</b>
</FWO2-2011-03-03T22-53-47>
</dataroot>

Answer 1

try xsd2db at www.sourceforge.net