SQL How to CAST XML Column to read multiple rows

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)

SQL How to CAST XML Column to read multiple rows

Author

Topic

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-04-10 : 17:11:24

I have an XML column with multiple records on one row. It is currently reading the first record only. How do I make it read all records under a given node.

Here is a sample XML row:


<packet Id="777777"><presentation compression="" encryption=""><set><Data><Fifo>
<Record id="12345" rkeys="P  " rtypes="P" dstfes="2" local="2013-04-10T07:51:00" ptyp="9" entry="2" assnid="1655902"></Record>
<Record id="12346" rkeys="P  " rtypes="P" dstfes="2" local="2013-04-10T07:52:00" ptyp="9" entry="2" assnid="1142882"></Record>
</Fifo></Data></set></presentation></packet>

Here is my query that only reads the first record:


SELECT m.v.value('(.//Record/@assnid)[1]', 'nvarchar(100)') As id,
m.v.value('(.//Record/@rkeys)[1]', 'nvarchar(100)') As key,
m.v.value('(.//Record/@entry)[1]', 'nvarchar(100)') As entry,
m.v.value('(.//Record/@local)[1]', 'nvarchar(100)') As local,
Left(m.v.value('(.//Record/@local)[1]', 'nvarchar(100)'),10) As Date,
Right(m.v.value('(.//Record/@local)[1]', 'nvarchar(100)'),8) As time,
m.v.value('(.//Record/@rtyp)[1]', 'nvarchar(100)') As type



FROM (SELECT CAST(XMLCOLUMN as xml) as col1 from XMLTABLE)t
CROSS APPLY col1.nodes('/packet')m(v)

How do I make it read all records under the packet node?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-10 : 17:24:34

Change to this:

SELECT m.v.value('(./@assnid)[1]', 'nvarchar(100)') As id,
m.v.value('(./@rkeys)[1]', 'nvarchar(100)') As key,
m.v.value('(./@entry)[1]', 'nvarchar(100)') As entry,
m.v.value('(./@local)[1]', 'nvarchar(100)') As local,
Left(m.v.value('(./@local)[1]', 'nvarchar(100)'),10) As Date,
Right(m.v.value('(./@local)[1]', 'nvarchar(100)'),8) As time,
m.v.value('(./@rtyp)[1]', 'nvarchar(100)') As type



FROM (SELECT CAST(XMLCOLUMN as xml) as col1 from XMLTABLE)t
CROSS APPLY col1.nodes('//Record')m(v)

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-04-11 : 10:24:04

Thank You so much James! that worked perfect.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-11 : 11:20:44

You are very welcome - glad to help.

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-11-11 : 09:45:56

I have been running this query for a while now. But I did notice a performance issue when returning rows (avaraging 8 min to return 1600 rows).


SELECT createdDate,TyepID
m.v.value('(./@assnid)[1]', 'nvarchar(100)') As id,
m.v.value('(./@rkeys)[1]', 'nvarchar(100)') As key,
m.v.value('(./@entry)[1]', 'nvarchar(100)') As entry,
m.v.value('(./@local)[1]', 'nvarchar(100)') As local,
Left(m.v.value('(./@local)[1]', 'nvarchar(100)'),10) As Date,
Right(m.v.value('(./@local)[1]', 'nvarchar(100)'),8) As time,
m.v.value('(./@rtyp)[1]', 'nvarchar(100)') As type
FROM (SELECT CAST(XMLCOLUMN as xml) as col1 from XMLTABLE)t
CROSS APPLY col1.nodes('//Record')m(v)
where createdDate is between date1 and date2
and m.v.value('(./@rkeys)[1]', 'nvarchar(100)')= 'Z'
AND TypeID = '1'

Any suggestions on how to best extract the XML data more efficently?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 09:54:15

quote:

Originally posted by emyk

I have been running this query for a while now. But I did notice a performance issue when returning rows (avaraging 8 min to return 1600 rows).


SELECT createdDate,TyepID
m.v.value('(./@assnid)[1]', 'nvarchar(100)') As id,
m.v.value('(./@rkeys)[1]', 'nvarchar(100)') As key,
m.v.value('(./@entry)[1]', 'nvarchar(100)') As entry,
m.v.value('(./@local)[1]', 'nvarchar(100)') As local,
Left(m.v.value('(./@local)[1]', 'nvarchar(100)'),10) As Date,
Right(m.v.value('(./@local)[1]', 'nvarchar(100)'),8) As time,
m.v.value('(./@rtyp)[1]', 'nvarchar(100)') As type
FROM (SELECT CAST(XMLCOLUMN as xml) as col1 from XMLTABLE)t
CROSS APPLY col1.nodes('//Record')m(v)
where createdDate is between date1 and date2
and m.v.value('(./@rkeys)[1]', 'nvarchar(100)')= 'Z'
AND TypeID = '1'

Any suggestions on how to best extract the XML data more efficently?

If your xml document structure is consistent use
/packet/record
this will make search easier compared to //record where it searches all possible paths for record node
secondly consider creating XML index on paths where you want to search

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources