xml data to SQL server

[theresatan]

Hi:

I wrote a script to import xml data to sql server 2000, every thing works fine when the data file like this:

<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>
</ROOT>

But it return Null value for all the fiels when data file like that:
<ROOT>
<Customers CustomerID="1111" CompanyName="Sean Chai" City="NY" >
</Customers>
<Customers CustomerID="1112" CompanyName="Tom Johnston" City="LA" >
</Customers>
</ROOT>

Following is my xsd file:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="Customers" sql:relation="Cust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Any idea to fix it?

I need it work for the second xml format.

Thank you very much.

Theresa

[sheehan]

Hi Theresa,

You'll need to make a change to the schema in order for the second xml to be accepted.

The following would work.

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" elementFormDefault="qualified" attributeFormDefault="unqualified">
	<xsd:element name="Customers" sql:relation="Cust">
		<xsd:complexType>
			<xsd:attribute name="CustomerID" type="xsd:integer"/>
			<xsd:attribute name="CompanyName" type="xsd:integer"/>
			<xsd:attribute name="City" type="xsd:integer"/>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

I attached a snapshot of how the schema looks in the schema design view of XMLSpy by Altova. You'll need to enable Microsoft sql server extensions in order to load your file in the schema design view. If you need help, let me know. You can also grab a free trial period of XMLSpy from their website at, Download XMLSpy Professional Edition I used this product because it's so easy to make any changes to your schema from the schema design view!

Jerry Sheehan

[theresatan]

Thank you very much.
The problem got solved

Theresa