Exporting XML data as a table in SQL Server Express

Hi there!

This is a part of the XML file that I have:

<?xml version="1.0" encoding="Windows-1252" standalone="yes" ?>

- <NewDataSet>

- <xschema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true">

- <xs:complexType>

- <xs:choice maxOccurs="unbounded">

- <xs:element name="HdrStateProv">

- <xs:complexType>

- <xsequence>

<xs:element name="stateProvID" type="xs:int" minOccurs="0" />

<xs:element name="stateProvNme" type="xstring" minOccurs="0" />

<xs:element name="CountryID" type="xs:int" minOccurs="0" />

</xsequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xschema>

- <HdrStateProv>

<stateProvID>34</stateProvID>

<stateProvNme>Alabama</stateProvNme>

<CountryID>225</CountryID>

</HdrStateProv>

As you can see it just has all the 50 states and I have a datatable called HdrStateProv with three fields, stateProvID, stateProvNme, CountryID in my SQL Server Express. How would I import(map) this data there? I tried the sqlbulkimport KB article but does that not seem to work. Thanks for your time!

Do you mean that sqlbulkimport is non-functional or that it could not import this XML format?

I believe that the bcp utility can import XML, search through Books Online for 'bcp utility' and that should get you started.

Mike