Importing XML into table

Question

Importing XML into table

ztuner-7

SSC Enthusiast

Points: 122
More actions
August 18, 2010 at 7:07 am

#74306

Is there any way to import XML below into a formatted table so the table looks something like (Sub,CUSID,ISSN,YEAR,RATE)
Thanks
Here is the XML
------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<DataSet xmlns="http://services.myservice.com">
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Sub">
<xs:complexType>
<xs:sequence>
<xs:element name="CUSID" type="xs:string" minOccurs="0"/>
<xs:element name="ISSN" type="xs:string" minOccurs="0"/>
<xs:element name="YEAR" type="xs:string" minOccurs="0"/>
<xs:element name="RATE" type="xs:string" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns="">

<CUSID>90000024</CUSID>
<ISSN>00131350</ISSN>
<YEAR>2005</YEAR>
<RATE>AS</RATE>


<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2005</YEAR>
<RATE>AS</RATE>


<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2006</YEAR>
<RATE>AS</RATE>


<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2007</YEAR>
<RATE>AS</RATE>


<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2008</YEAR>
<RATE>AS</RATE>

</NewDataSet>
</diffgr:diffgram>
</DataSet>

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 737892 More actions · Answer 1

You would need to read the XML and split into rows. There is a task in SSIS that can help here. Is this a one time thing or a repeatable process?

Steve Jones - SSC Editor SSC Guru Points: 737892 More actions · Answer 2

Sorry, forgot to include a link to help: http://www.sqlservercentral.com/Forums/Topic854028-148-1.aspx

ztuner-7 SSC Enthusiast Points: 122 More actions · Answer 3

Thanks for reply. It will be an ongoing process (XML data will be process daily). I'll check the link.

Thanks

ztuner-7 SSC Enthusiast Points: 122 More actions · Answer 4

I was hoping tu use OPENXML and T-SQL. It will ebnice to create the whole impor tprocess as a stored procedure. Hmmm. Anyone can help with OPENXML?

LutzM SSC Guru Points: 107049 More actions · Answer 5

I would use XQuery over OpenXML.

However, I had to change your sample data:

Your row < NewDataSet xmlns="" > will result in an XQuery error since you need to use valid namespaces once you started using it. If the namespace gets resetted in the middle of the xml file it'll throw an error. Therefore, I changed it to < NewDataSet xmlns="http://services.myservice.com" >

DECLARE @xml XML

SET @xml='your xml data'

;WITH XMLNAMESPACES

(

'urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr,

'urn:schemas-microsoft-com:xml-msdata' AS msdata,

DEFAULT 'http://services.myservice.com'

)

SELECT

c.value('@diffgr:id[1]','varchar(10)') AS Sub,

c.value('CUSID[1]','varchar(10)') AS CUSID,

c.value('ISSN[1]','varchar(10)') AS ISSN,

c.value('YEAR[1]','INT') AS YEAR,

c.value('RATE[1]','varchar(10)') AS RATE

FROM @xml.nodes('DataSet/diffgr:diffgram/NewDataSet/Sub') T(c)

/* result set:

SubCUSIDISSNYEARRATE

Sub190000024001313502005AS

Sub290000024030600122005AS

Sub390000024030600122006AS

Sub490000024030600122007AS

Sub590000024030600122008AS

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ztuner-7 SSC Enthusiast Points: 122 More actions · Answer 6

Hall of Fame,

Thanks for your help. It is working with XQUERY and that is fantastic. You saved me probably a week and more work on this XML import.

Thanks again.

ztuner-7 SSC Enthusiast Points: 122 More actions · Answer 7

Hi Hall Of Fame, again me. Sorry to bother you again, can you help with the last XML file to import it into SQL table.

Here is the file:

--------------------------------------

<?xml version="1.0" encoding="utf-8"?>

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

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

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="Customers">

<xs:complexType>

<xs:sequence>

<xs:element name="CUSID" type="xs:string" minOccurs="0" />

<xs:element name="ORGNAME" type="xs:string" minOccurs="0" />

<xs:element name="CITY" type="xs:string" minOccurs="0" />

<xs:element name="COUNTRY" type="xs:string" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

<ORGNAME>TEST1 UNIVERSITY</ORGNAME>

<CITY>Coventry</CITY>

<COUNTRY>United Kingdom</COUNTRY>

</Customers>

<ORGNAME>TEST2 UNIVERSITY</ORGNAME>

<CITY>Gif Sur Yvette Cedex</CITY>

<COUNTRY>France</COUNTRY>

</Customers>

<ORGNAME>TEST3 UNIVERSITY</ORGNAME>

<CITY>Baltimore</CITY>

<COUNTRY>United States of America</COUNTRY>

</Customers>

<ORGNAME>TEST4 UNIVERSITY</ORGNAME>

<CITY>Kansas City</CITY>

<COUNTRY>United States of America</COUNTRY>

</Customers>

<ORGNAME>TEST5 UNIVERSITY</ORGNAME>

<CITY>Bangalore</CITY>

<COUNTRY>India</COUNTRY>

</Customers>

<ORGNAME>TEST5 UNIVERSITY</ORGNAME>

<CITY>Shatin</CITY>

</Customers>

<ORGNAME>TEST6 UNIVERSITY</ORGNAME>

<CITY>Glasgow</CITY>

<COUNTRY>United Kingdom</COUNTRY>

</Customers>

<ORGNAME>TEST6 UNIVERSITY</ORGNAME>

<CITY>Monroeville</CITY>

<COUNTRY>United States of America</COUNTRY>

</Customers>

<ORGNAME>TEST7 UNIVERSITY</ORGNAME>

<CITY>Oldenburg</CITY>

<COUNTRY>Germany</COUNTRY>

</Customers>

<ORGNAME>TEST8 UNIVERSITY</ORGNAME>

<CITY>Wetherby</CITY>

<COUNTRY>United Kingdom</COUNTRY>

</Customers>

<ORGNAME>TEST9 UNIVERSITY</ORGNAME>

<CITY>New Haven</CITY>

<COUNTRY>United States of America</COUNTRY>

</Customers>

</NewDataSet>

</diffgr:diffgram>

</DataSet>

LutzM SSC Guru Points: 107049 More actions · Answer 8

Hi Forum Newbie ( )

At a first glance: it's the same issue like the first file:

There is a line where the default namespace is reset to an empty string: < NewDataSet xmlns="" >

Change that to either < NewDataSet xmlns="http://services.myservice.com" > or < NewDataSet >.

After that you just would need to adjust the previous query to match the new node names. Give it a try and post back where you get stuck.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ztuner-7 SSC Enthusiast Points: 122 More actions · Answer 9

Thanks again. I got it all working as you suggested. Thansk for quick repaly. That was very helpful.

Thanks again for your time.

Importing XML into table

Cookies on SQLServerCentral