| Author |
 Topic  |
|
|
emyk
Yak Posting Veteran
57 Posts |
 Posted - 04/10/2013 : 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?
|
Edited by - emyk on 04/10/2013 17:13:24
|
|
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 04/10/2013 : 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 - 04/11/2013 : 10:24:04
|
Thank You so much James! that worked perfect.
|
Edited by - emyk on 04/11/2013 10:24:23 |
|
|
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 04/11/2013 : 11:20:44
|
| You are very welcome - glad to help. |
|
|
|
emyk
Yak Posting Veteran
57 Posts |
Posted - 11/11/2013 : 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
India
52326 Posts |
Posted - 11/11/2013 : 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
|
|
|
| |
Topic |
|
|
|
SQL How to CAST XML Column to read multiple rows