Ordinal position within XML
-
I'm using the XQuery nodes() method to shred XML, but in addition to the node value, I want to return the ordinal position. For example,
DECLARE @xml xml
SET @xml = N'
<ParameterList><Parameter ParameterName="Year">
<pv>2004</pv>
</Parameter>
<Parameter ParameterName="Make">
<pv>Ford</pv>
<pv>Pontiac</pv>
</Parameter>
</ParameterList>'
SELECTdoc.col.value('../@ParameterName', 'varchar(100)') ParameterName,
doc.col.value('.', 'varchar(max)') pv
FROM @xml.nodes('/ParameterList/Parameter[@ParameterName="Make"]/pv') doc(col)
This returns
ParameterName pv
--------------- -------
Make Ford
Make Pontiac
but I want to return this, indicating what order the elements are within the xml node
ParameterName pv SortOrder
--------------- --------- ---------
Make Ford 1
Make Pontiac 2
Any ideas?
-
Here's a possible solution:
Please note that I used 'Make' as a hard coded value since it's used to limit the result set anyway (@ParameterName="Make"). therefore it doesn't make sense from my point of view to climb up the xml hierarchy just to find the value we've just used as a pseudo Where-clause...
SELECT'Make' as ParameterName,
doc.col.value('.','VARCHAR(10)') AS Name,
p.number as SortOrder
FROM
master..spt_values p
cross APPLY @xml.nodes('/ParameterList/Parameter[@ParameterName="Make"]/pv[position()=sql:column("number")]') doc(col)
where p.type = 'p'
-
I see you are using the spt_values table as simply a known sequence of numbers. The sql:column() XQuery extension function I've seen before but hadn't understood until now the whole deal with the CROSS APPLY coding. Thanks very much!
-
billj-705548 (12/14/2010)
I see you are using the spt_values table as simply a known sequence of numbers. The sql:column() XQuery extension function I've seen before but hadn't understood until now the whole deal with the CROSS APPLY coding. Thanks very much!Glad I could help 😀
And thank you for the feedback! It's always great to see someone actually analyzing the code instead of simply cut'n'paste... Great job!!!
-
Another way
SELECTdoc.col.value('../@ParameterName', 'varchar(100)') ParameterName,
doc.col.value('.', 'varchar(max)') pv,
doc.col.value('count(for $a in . return $a/../pv[.<<$a])+1', 'int') SortOrder
FROM @xml.nodes('/ParameterList/Parameter[@ParameterName="Make"]/pv') doc(col)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you for that method as well Mark. If I'm understanding correctly, this method counts the number of ordinals before the current one -- though I've got to do some learning on the syntax.
In the first post by Lutz, if the "numbers" table is too large (say a million records) it could take a while to chug through each number before displaying the output. In the second post by Mark, if the number of elements grows too large, it could take a while to do all the counting. Am I right?
Do either of you know (or anyone else out there) if when shredding XML, the order of the elements persists without specifying an ORDER BY clause? Normally without specifying ORDER BY in a SELECT statement you aren't guaranteed an order, but is the same still true even when shredding an XML document which by definition is ordered?
I wondered if the order could be preserved this way:
CREATE TABLE #ParameterValues(
[ParameterName] varchar(100) NOT NULL,
[ParameterValue] varchar(100) NOT NULL,
[SortOrder] [int] IDENTITY(1,1) NOT NULL
)
insert into #ParameterValues
(
ParameterName,
ParameterValue
)
SELECT
doc.col.value('../@ParameterName', 'varchar(100)') ParameterName,
doc.col.value('.', 'varchar(max)') ParameterValue
FROM @xml.nodes('/ParameterList/Parameter[@ParameterName="Make"]/pv') doc(col)
-
billj-705548 (12/15/2010)
Thank you for that method as well Mark. If I'm understanding correctly, this method counts the number of ordinals before the current one -- though I've got to do some learning on the syntax.In the first post by Lutz, if the "numbers" table is too large (say a million records) it could take a while to chug through each number before displaying the output. In the second post by Mark, if the number of elements grows too large, it could take a while to do all the counting. Am I right?
Do either of you know (or anyone else out there) if when shredding XML, the order of the elements persists without specifying an ORDER BY clause? Normally without specifying ORDER BY in a SELECT statement you aren't guaranteed an order, but is the same still true even when shredding an XML document which by definition is ordered? ...
Now I'm confused...
Can you show me any official reference supporting the argument that an XML dox is ordered by definition? There is no such information available at www.w3.org[/url].
Consider the following short xml doc:
<root><name>LutzM</name>
<name>billj-705548</name>
</root>
As per XML def this is not different to
<root><name>billj-705548</name>
<name>LutzM</name>
</root>
If you need to know the sequence number of an attribute you'd need to add this information (e.g. by adding a sequence attribute). I actually doubt you'll find any proof that the order will be maintained under each and every circumstance.
Regarding the size of the numbers table: it's up to you to reduce the size simply by adding a WHERE clause. But you also need to make sure never to exceed that number in your xml doc. The best way to figure out the impact of the numbers table size is to test it. I'm confident you'll find the impact not as relevant as you think right now...
-
LutzM (12/15/2010)
billj-705548 (12/15/2010)
Thank you for that method as well Mark. If I'm understanding correctly, this method counts the number of ordinals before the current one -- though I've got to do some learning on the syntax.In the first post by Lutz, if the "numbers" table is too large (say a million records) it could take a while to chug through each number before displaying the output. In the second post by Mark, if the number of elements grows too large, it could take a while to do all the counting. Am I right?
Do either of you know (or anyone else out there) if when shredding XML, the order of the elements persists without specifying an ORDER BY clause? Normally without specifying ORDER BY in a SELECT statement you aren't guaranteed an order, but is the same still true even when shredding an XML document which by definition is ordered? ...
Now I'm confused...
Can you show me any official reference supporting the argument that an XML dox is ordered by definition? There is no such information available at www.w3.org[/url].
Consider the following short xml doc:
<root><name>LutzM</name>
<name>billj-705548</name>
</root>
As per XML def this is not different to
<root><name>billj-705548</name>
<name>LutzM</name>
</root>
If you need to know the sequence number of an attribute you'd need to add this information (e.g. by adding a sequence attribute). I actually doubt you'll find any proof that the order will be maintained under each and every circumstance.
Regarding the size of the numbers table: it's up to you to reduce the size simply by adding a WHERE clause. But you also need to make sure never to exceed that number in your xml doc. The best way to figure out the impact of the numbers table size is to test it. I'm confident you'll find the impact not as relevant as you think right now...
Document order is preserved according to here
http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx
"XML values are stored in an internal format ... to support the XML model characteristics, such as document order ..."
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply