Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Importing XML into table

Author  Topic 

sholtan
Starting Member

4 Posts
Posted - 2010-08-18 : 09:04:54
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="">
<Sub diffgr:id="Sub1" msdata:rowOrder="0">
<CUSID>90000024</CUSID>
<ISSN>00131350</ISSN>
<YEAR>2005</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub2" msdata:rowOrder="1">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2005</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub3" msdata:rowOrder="2">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2006</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub4" msdata:rowOrder="3">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2007</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub5" msdata:rowOrder="4">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2008</YEAR>
<RATE>AS</RATE>
</Sub>
</NewDataSet>
</diffgr:diffgram>
</DataSet>

MSquared
Yak Posting Veteran

52 Posts
Posted - 2010-08-18 : 12:28:52
can you change the schema? The diffgr:diffgram tag is causing issues I can't seem to get around. If you can remove the comma, then you can get it to work.


DECLARE @index int
DECLARE @XMLdoc varchar(8000)

SET @XMLdoc ='<?xml version="1.0" encoding="utf-8"?>
<DataSet>
<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>
<diffgrdiffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns="">
<Sub diffgr:id="Sub1">
<CUSID>90000024</CUSID>
<ISSN>00131350</ISSN>
<YEAR>2005</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub2">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2005</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub3">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2006</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub4">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2007</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub5">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2008</YEAR>
<RATE>AS</RATE>
</Sub>
</NewDataSet>
</diffgrdiffgram>
</DataSet>'
 
EXEC sp_xml_preparedocument @index OUTPUT, @XMLdoc
 
SELECT *
FROM OPENXML (@index, 'DataSet/diffgrdiffgram/NewDataSet/Sub')
WITH (Name varchar(100) 'CUSID' , ISSN Varchar(10) 'ISSN', Year varchar(4) 'YEAR', Rate varchar(10) 'RATE')                
 
EXEC sp_xml_removedocument @index
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-08-19 : 03:00:41
Just try this -
No need to change anything in XML data.


DECLARE @InXmlDoc AS xml
SET @InXmlDoc = '<?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="">
<Sub diffgr:id="Sub1" msdata:rowOrder="0">
<CUSID>90000024</CUSID>
<ISSN>00131350</ISSN>
<YEAR>2005</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub2" msdata:rowOrder="1">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2005</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub3" msdata:rowOrder="2">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2006</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub4" msdata:rowOrder="3">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2007</YEAR>
<RATE>AS</RATE>
</Sub>
<Sub diffgr:id="Sub5" msdata:rowOrder="4">
<CUSID>90000024</CUSID>
<ISSN>03060012</ISSN>
<YEAR>2008</YEAR>
<RATE>AS</RATE>
</Sub>
</NewDataSet>
</diffgr:diffgram>
</DataSet>'

SELECT  fileds.value('CUSID[1]', 'varchar(30)') AS CUSID,
		fileds.value('ISSN[1]', 'varchar(30)') AS ISSN,
		fileds.value('YEAR[1]', 'varchar(30)') AS YEAR,
		fileds.value('RATE[1]', 'varchar(30)') AS RATE
FROM	@InXmlDoc.nodes('//NewDataSet/Sub') as  xmldata(fileds)



Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2010-08-19 : 03:37:40
quote:
Originally posted by MSquared

EXEC sp_xml_preparedocument @index OUTPUT, @XMLdoc
 
SELECT *
FROM OPENXML (@index, 'DataSet/diffgrdiffgram/NewDataSet/Sub')
WITH (Name varchar(100) 'CUSID' , ISSN Varchar(10) 'ISSN', Year varchar(4) 'YEAR', Rate varchar(10) 'RATE')
 
EXEC sp_xml_removedocument @index
This is a SQL Server 2008 forum, and you should try to learn the new ways to deal with XML data. The syntax your use is introduced with sql server 2000.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

sholtan
Starting Member

4 Posts
Posted - 2010-08-19 : 06:18:38
Tahsnk for help everyone. It work with the XML submitted but I made mistake in the file: Instead of xmlns="" is should be xmlns="http://services.myservices.com" in two places -

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

and


<NewDataSet xmlns="">


should be

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

and


<NewDataSet xmlns="http://services.myservices.com"

Would OPNEXML be different now?

Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2010-08-19 : 06:22:12
Do not use OPENXML!
Use the new methods as posted by Vaibhav.

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site