xml data file

[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

 

[tsuji]

[1] This is how you construct the alternative Customers.
[tt]
<xsd:simpleType name="emptyType">
<xsd:restriction base="xsd:string">
<xsdattern value="(\s)*" />
</xsd:restriction>
</xsd:simpleType>

<xsd:element name="Customers" sql:relation="Cust" >
<xsd:complexType>
<xsd:simpleContent>
<xsd:extension base="emptyType">
<xsd:attribute name="CustomerID" type="xsd:integer" />
<xsd:attribute name="CompanyName" type="xsd:string" />
<xsd:attribute name="City" type="xsd:string" />
</xsd:extension>
</xsd:simpleContent>
</xsd:complexType>
</xsd:element>
[/tt]
[2] After that global element constructed, you can refer to it in the ROOT, like this.
[tt]
<xsd:element name="ROOT">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="Customers" maxOccurs="unbounded" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
[/tt]

 

[simonchristieis]

Whats this:

<xsdattern value="(\s)*" />


I've not seen this before

 

[tsuji]

That is a bit of a safety measure, as the default behaviour of how processor treats whitespaces is actually somehow process-dependent. Hence, it would allow all these realizations.
[tt]
<Customers CustomerID="1111" CompanyName="Sean Chai" City="NY">
</Customers>
<Customers CustomerID="1111" CompanyName="Sean Chai" City="NY" />
<Customers CustomerID="1111" CompanyName="Sean Chai" City="NY"></Customers>
[/tt]
The value string is literally a regular expression providing an intersection with other procedural language in that technology.