Unfortunately, when reading / shredding XML document from SQL Server, there are no guarantees in the order in which SQL Server will read the XML nodes. This can prove particularly troublesome if your code or import procedure relies on a specific order. When you run your script, the order it retrieves the XML is random and can be the cause of some intermittent results – which can contribute to hair loss.
Your first thought, may be to try and use the “position()” function in your retrieval script, but alas, this will not work.
The below solution is a bit of a hack, but it seems to work regardless of how many times I run the script and I can guarantee the order in which the node information was presented in the XML.
First, let’s assume you have an XML parameter, or XML column in a table in your database. Here, we are just going to create a dummy XML data type for demonstration purposes.
DECLARE @Xml XML
SET @Xml = ‘
<Data>
<Field>
<Name>Person</Name>
<Value>Joe Bloggs</Value>
</Field>
<Field>
<Name>Telephone</Name>
<Value>12345678</Value>
</Field>
<Field>
<Name>Address</Name>
<Value>1 Blah Street</Value>
</Field>
</Data>
‘
Now, we are going to create a temporary table which is simply full of sequential numbers which should cater for the number of nodes you are expecting. Note, in my case, I was reading XML describing the fields I was expecting, so 100 numbers here is more than enough. You may require more.
Here I create the table to consist of 100 numbers (as that is more than enough to cover the number of fields I am expecting).
DECLARE @Numbers TABLE(num int identity(1,1))
INSERT @Numbers DEFAULT VALUES;
WHILE SCOPE_IDENTITY() < 100
INSERT @Numbers DEFAULT VALUES;
Then, we are going to perform the query on the XML, to retrieve the field information, combined with the order in which the node is stored in the XML.
SELECT *
INTO #Fields
FROM
(
SELECT
–Fields.Field.value(‘position()’, ‘int’) AS Order, — Unfortunately, this will not work!
n.num AS [Order],
Fields.Field.value(‘./Name[1]‘, ‘varchar(50)’) AS FieldName,
Fields.Field.value(‘./Value[1]‘, ‘varchar(50)’) AS FieldValue
FROM @Numbers n
CROSS APPLY @Xml.nodes(‘/Data/Field[sql:column("n.num")]‘) AS Fields(Field)
) AS f
SELECT * FROM #Fields
DROP TABLE #Fields
Below is an example of what is returned:
Ordered Node Values in Table
As you can see, it provides you now with a temporary table and column that defines the order in which the XML node was presented in the XML. You can then sort this table by the order column if you so wish, join by it, or whatever it is you need to do!
Hope this helps,
Stefan.
For more Web Code, ASP.NET, SQL Server and other development tips, please check back here at webcodeblog.com often.
