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)
Go to Top of Page

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-04-11 : 10:24:04
Thank You so much James! that worked perfect.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-11 : 11:20:44
You are very welcome - glad to help.
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -