SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL How to CAST XML Column to read multiple rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emyk
Yak Posting Veteran

51 Posts

Posted - 04/10/2013 :  17:11:24  Show Profile  Reply with Quote
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?

Edited by - emyk on 04/10/2013 17:13:24

James K
Flowing Fount of Yak Knowledge

3660 Posts

Posted - 04/10/2013 :  17:24:34  Show Profile  Reply with Quote
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

51 Posts

Posted - 04/11/2013 :  10:24:04  Show Profile  Reply with Quote
Thank You so much James! that worked perfect.


Edited by - emyk on 04/11/2013 10:24:23
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3660 Posts

Posted - 04/11/2013 :  11:20:44  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

emyk
Yak Posting Veteran

51 Posts

Posted - 11/11/2013 :  09:45:56  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/11/2013 :  09:54:15  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000