Read XML through Query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Read XML through Query

Author

Topic

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-25 : 03:16:55

How to read XML file and stored in Tables?

====================================================
you realize you've made a mistake, take immediate steps to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 03:19:55

by reading do you mean extracting data from xml? is datatype of column conatining xml value xml? if yes, you could use nodes() or query() functions to get data from xml nodes by specifying the Xpath.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 03:21:09

See this example. It has namespaces also, but you can ignore that.
http://weblogs.sqlteam.com/peterl/archive/2008/11/06/Using-namespaces-in-XML-queries.aspx

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-25 : 03:23:41

Already I write XML file from (DataGridView) Front End Application. Again I will show that in Another Grid, Thats why i need to reload into table through Query.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 03:26:47

quote:
Originally posted by karthickbabu

Already I write XML file from (DataGridView) Front End Application. Again I will show that in Another Grid, Thats why i need to reload into table through Query.
====================================================
you realize you've made a mistake, take immediate steps to correct it.

reload as a whole xml doc?
then see below

http://msdn.microsoft.com/en-us/library/ms191184.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 03:32:50

I must admit I have no idea what you are trying to do, but you can do an INSERT with XML data into a column just as any other datatype.
And you can read the XML data from the column just as any other datatype.

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-25 : 05:25:21

Not like that

SELECT * FROM TableName - List the records

Table can be export as XML file then again read that XML file and stored in temp tables

SELECT * FROM TempTableName - Again it list the same data like TableName

====================================================
you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 05:29:45

Store the WHOLE xml file content in a column in a record in a database.
Why save the XML content as a file?

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-25 : 05:43:59

Hi peter

I couldn't get your point

====================================================
you realize you've made a mistake, take immediate steps to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:47:50

quote:
Originally posted by karthickbabu

Not like that

SELECT * FROM TableName - List the records

Table can be export as XML file then again read that XML file and stored in temp tables

SELECT * FROM TempTableName - Again it list the same data like TableName

====================================================
you realize you've made a mistake, take immediate steps to correct it.

can you explain whats purpose behind doing like this? putt in a table, again export to file,again list??

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-25 : 06:53:53

Its write as XML then it store into temp table and display in front end.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-25 : 07:34:19

I refer BOL and try like as below

1. Read XML and stored in table

CREATE TABLE T (IntCol int, XmlCol xml)
GO

INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(BULK '\\MyServerName\Sharable\SalesReport.xml',SINGLE_BLOB) AS x
SELECT * FROM T

It stores the Content of XML file in xmlCol of Table T

2. This is my xml content

<NewDataSet>
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="NewDataSet">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="SalesPersonReport">
<xs:complexType>
<xs:sequence>
<xs:element name="PersonName" type="xs:string" default="" minOccurs="0" />
<xs:element name="City" type="xs:string" default="" minOccurs="0" />
<xs:element name="SalesAmountForBoost" type="xs:int" default="0" minOccurs="0" />
<xs:element name="SalesAmountForHorlicks" type="xs:int" default="0" minOccurs="0" />
<xs:element name="TotalSales" type="xs:int" default="0" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<SalesPersonReport>
<PersonName>John</PersonName>
<City>City1</City>
<SalesAmountForBoost>5645</SalesAmountForBoost>
<SalesAmountForHorlicks>8989798</SalesAmountForHorlicks>
<TotalSales>8995443</TotalSales>
</SalesPersonReport>
<SalesPersonReport>
<PersonName>Babu</PersonName>
<City>City2</City>
<SalesAmountForBoost>786786</SalesAmountForBoost>
<SalesAmountForHorlicks>98797</SalesAmountForHorlicks>
<TotalSales>885583</TotalSales>
</SalesPersonReport>
<SalesPersonReport>
<PersonName>karthick</PersonName>
<City>madurai</City>
<SalesAmountForBoost>839</SalesAmountForBoost>
<SalesAmountForHorlicks>8998</SalesAmountForHorlicks>
<TotalSales>9837</TotalSales>
</SalesPersonReport>
</NewDataSet>

3. Read XML Content and stores in SalesPerson Table

CREATE TABLE SalesPerson( Name VARCHAR(20), City VARCHAR(20), Boost INT, Horlicks INT, TotalSales INT)

DECLARE @docHandle int
DECLARE @xmlDocument XML --nvarchar(max) -- or xml type
SELECT @xmlDocument = XmlCol FROM T

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT SalesPerson
SELECT * FROM OPENXML(@docHandle, N'/NewDataSet/SalesPersonReport') WITH SalesPerson

4. SELECT * FROM SalesPerson

Name City Boost Horlicks TotalSales
-------------------- -------------------- ----------- ----------- -----------
NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL

(3 row(s) affected)

It insert three records but only NULL Values.

Any idea about this

====================================================
you realize you've made a mistake, take immediate steps to correct it.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-26 : 00:06:04

Hi Expert,

Anyone have idea about the previous post?

====================================================
you realize you've made a mistake, take immediate steps to correct it.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-26 : 01:10:17

This article is a little outdated, but is a very good list of ways to do what you want
http://www.yukonxml.com/articles/sql2000xml/

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-26 : 01:12:55

Thank You, I will go through and check that one.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 01:46:50

[code]declare @xml xml
set @xml='<NewDataSet>
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="NewDataSet">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="SalesPersonReport">
<xs:complexType>
<xs:sequence>
<xs:element name="PersonName" type="xs:string" default="" minOccurs="0" />
<xs:element name="City" type="xs:string" default="" minOccurs="0" />
<xs:element name="SalesAmountForBoost" type="xs:int" default="0" minOccurs="0" />
<xs:element name="SalesAmountForHorlicks" type="xs:int" default="0" minOccurs="0" />
<xs:element name="TotalSales" type="xs:int" default="0" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<SalesPersonReport>
<PersonName>John</PersonName>
<City>City1</City>
<SalesAmountForBoost>5645</SalesAmountForBoost>
<SalesAmountForHorlicks>8989798</SalesAmountForHorlicks>
<TotalSales>8995443</TotalSales>
</SalesPersonReport>
<SalesPersonReport>
<PersonName>Babu</PersonName>
<City>City2</City>
<SalesAmountForBoost>786786</SalesAmountForBoost>
<SalesAmountForHorlicks>98797</SalesAmountForHorlicks>
<TotalSales>885583</TotalSales>
</SalesPersonReport>
<SalesPersonReport>
<PersonName>karthick</PersonName>
<City>madurai</City>
<SalesAmountForBoost>839</SalesAmountForBoost>
<SalesAmountForHorlicks>8998</SalesAmountForHorlicks>
<TotalSales>9837</TotalSales>
</SalesPersonReport>
</NewDataSet>'

select t.v.value('PersonName[1]','varchar(100)') AS Name,
t.v.value('City[1]','varchar(100)') AS City,
t.v.value('SalesAmountForBoost[1]','varchar(100)') AS Boost,
t.v.value('SalesAmountForHorlicks[1]','varchar(100)') AS Horlicks,
t.v.value('TotalSales[1]','varchar(100)') AS TotalSales
from @xml.nodes('/NewDataSet/SalesPersonReport')t(v)

output
----------------------------------------------------
Name City Boost Horlicks TotalSales
-----------------------------------------------------
John City1 5645 8989798 8995443
Babu City2 786786 98797 885583
karthick madurai 839 8998 9837[/code]

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-27 : 01:34:24

Great Visakh

I got some ideas in your query. Then I use the same and try like as below

USE tempdb
CREATE TABLE T (IntCol int, XmlCol xml)
GO

INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(BULK '\\MyServerName\Sharable\SalesReport.xml',SINGLE_BLOB) AS x

SELECT * FROM T

CREATE TABLE SalesPerson (PersonName VARCHAR(20), City VARCHAR(20), SalesAmountForBoost INT,
SalesAmountForHorlicks INT, TotalSales INT)

DECLARE @docHandle int
DECLARE @xmlDocument XML
SELECT @xmlDocument = XmlCol FROM T

INSERT SalesPerson
select t.v.value('PersonName[1]','varchar(100)') AS Name,
t.v.value('City[1]','varchar(100)') AS City,
t.v.value('SalesAmountForBoost[1]','varchar(100)') AS Boost,
t.v.value('SalesAmountForHorlicks[1]','varchar(100)') AS Horlicks,
t.v.value('TotalSales[1]','varchar(100)') AS TotalSales
from @xmlDocument.nodes('/NewDataSet/SalesPersonReport') t(v)

SELECT * FROM SalesPerson

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT SalesPerson
SELECT * FROM OPENXML(@docHandle, N'/NewDataSet/SalesPersonReport',2) WITH SalesPerson

SELECT * FROM SalesPerson

Both OPENXML and XML Function works fine, But still i have one doubt.
In case, I use the Variable Notation instead of Subscript notation in the query.
It shows NULL for all rows & columns.

I think both are same PersonName[1] is equal to @PersonName

select t.v.value('@PersonName','varchar(100)') AS Name,
t.v.value('@City','varchar(100)') AS City,
t.v.value('@SalesAmountForBoost','varchar(100)') AS Boost,
t.v.value('@SalesAmountForHorlicks','varchar(100)') AS Horlicks,
t.v.value('@TotalSales','varchar(100)') AS TotalSales
from @xmlDocument.nodes('/NewDataSet/SalesPersonReport') t(v)

Why its not working?

====================================================
you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 02:35:57

Using @ in front of names denoted ATTRIBUTES, not elements.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 02:44:47

[code]DECLARE @doc XML

SET @doc = '<NewDataSet>
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="NewDataSet">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="SalesPersonReport">
<xs:complexType>
<xs:sequence>
<xs:element name="PersonName" type="xs:string" default="" minOccurs="0" />
<xs:element name="City" type="xs:string" default="" minOccurs="0" />
<xs:element name="SalesAmountForBoost" type="xs:int" default="0" minOccurs="0" />
<xs:element name="SalesAmountForHorlicks" type="xs:int" default="0" minOccurs="0" />
<xs:element name="TotalSales" type="xs:int" default="0" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<SalesPersonReport year="2008">
<PersonName>John</PersonName>
<City>City1</City>
<SalesAmountForBoost>5645</SalesAmountForBoost>
<SalesAmountForHorlicks>8989798</SalesAmountForHorlicks>
<TotalSales>8995443</TotalSales>
</SalesPersonReport>
<SalesPersonReport year="2007">
<PersonName>Babu</PersonName>
<City>City2</City>
<SalesAmountForBoost>786786</SalesAmountForBoost>
<SalesAmountForHorlicks>98797</SalesAmountForHorlicks>
<TotalSales>885583</TotalSales>
</SalesPersonReport>
<SalesPersonReport year="2006">
<PersonName>karthick</PersonName>
<City>madurai</City>
<SalesAmountForBoost>839</SalesAmountForBoost>
<SalesAmountForHorlicks>8998</SalesAmountForHorlicks>
<TotalSales>9837</TotalSales>
</SalesPersonReport>
</NewDataSet>'

SELECT t.v.value('PersonName[1]', 'varchar(100)') AS Name,
t.v.value('City[1]', 'varchar(100)') AS City,
t.v.value('SalesAmountForBoost[1]', 'int') AS Boost,
t.v.value('SalesAmountForHorlicks[1]', 'int') AS Horlicks,
t.v.value('TotalSales[1]', 'int') AS TotalSales,
t.v.value('@year[1]', 'smallint') AS reportYear
FROM @doc.nodes('/NewDataSet/SalesPersonReport') AS t(v)[/code]

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-27 : 05:03:24

I refer in this link, they are using @Var and Var[1] separately

http://www.mssqltips.com/tip.asp?tip=1609

Which one is correct?

====================================================
you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 05:06:49

Exactly my point!

They are referring to ATTRIBUTES, not ELEMENTS.

quote:
DECLARE @xml xml
SET @xml = N'<polist>
<po ponumber="100" podate="2008-09-10" />
<po ponumber="101" podate="2008-09-11" />
</polist>'
SELECT
doc.col.value('@ponumber', 'nvarchar(10)') ponumber
,doc.col.value('@podate', 'datetime') podate
FROM @xml.nodes('/polist/po') doc(col)

E 12°55'05.63"
N 56°04'39.26"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources