xml data file format

[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

 

[gmmastros]

Are you using OpenXML?

If so, add a 3rd parameter with the value 1.

Ex.
OpenXML(@iDoc, 'Whetever', [!]1[/!])



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

in the first XML CustomerID is an element in the second XML CustomerID is an attribute

what happens when you use <xsd:attribute name =

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[theresatan]

when use '<xsd:attribute name =' replace '<xsd:element name='

I got following error:

Error: Schema: unable to load schema 'SampleSchema0.xml'. An error occurred (SampleSchema0.xml#/schema/element[1][@name = 'Customers']/complexType[1]/sequence[1]/attribute[1][@name = 'CustomerID']
Element 'xsd:attribute' is not allowed in this context.).
Code: 80004005
Source: Schema mapping

 

[theresatan]

George:

I tried OpenXML(@iDoc, 'myWhetever', 1)
it returned empty result set

Thanks!

Theresa

 

[SQLDenis]

when using attribute you need the @ sign when using elements you don't (or vice versa)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Try [!]3[/!]

Ex:

[COLOR=blue]Declare[/color] @XML [COLOR=blue]VarChar[/color](8000)
[COLOR=blue]Declare[/color] @iDoc [COLOR=blue]Int[/color]

[COLOR=blue]Set[/color] @XML = [COLOR=red]'<ROOT>
[/color]    <Customers CustomerID="1111"  CompanyName="Sean Chai" City="NY" >
    </Customers>
    <Customers CustomerID="1112"  CompanyName="Tom Johnston" City="LA" >
    </Customers>
</ROOT>[COLOR=red]'
[/color]
[COLOR=blue]EXEC[/color] sp_xml_preparedocument @idoc [COLOR=blue]OUTPUT[/color], @xml

[COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]   OpenXML(@iDoc, [COLOR=red]'ROOT/Customers'[/color], 3)
[COLOR=blue]With[/color]   (CustomerID [COLOR=blue]Int[/color],
       CompanyName [COLOR=blue]VarChar[/color](100),
       City [COLOR=blue]VarChar[/color](100)
       )

[COLOR=blue]Exec[/color] sp_xml_removedocument @idoc

[COLOR=blue]Declare[/color] @XML [COLOR=blue]VarChar[/color](8000)
[COLOR=blue]Declare[/color] @iDoc [COLOR=blue]Int[/color]

[COLOR=blue]Set[/color] @XML = [COLOR=red]'<ROOT>
[/color]  <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>[COLOR=red]'
[/color]
[COLOR=blue]EXEC[/color] sp_xml_preparedocument @idoc [COLOR=blue]OUTPUT[/color], @xml

[COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]   OpenXML(@iDoc, [COLOR=red]'ROOT/Customers'[/color], 3)
[COLOR=blue]With[/color]   (CustomerID [COLOR=blue]Int[/color],
       CompanyName [COLOR=blue]VarChar[/color](100),
       City [COLOR=blue]VarChar[/color](100)
       )

[COLOR=blue]Exec[/color] sp_xml_removedocument @idoc

Notice How both of them use 3.

If you have attributes, you should use 1. For elements, Use 2. OR you can use 3 and both work.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[theresatan]

Denis:

After I removed <xsd:sequence> and </xsd:sequence> too, then it works.

Thank you very much!

Theresa

 

[gmmastros]

I should also mention that the code I posted does work. However, with XML you need to be careful because XML is case sensitive. As 'SQL Server People', we get accustomed to non-case sensitive code. Unfortunately, that has a tendency to bite us some times.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom