Finding Node Order in Xml Document in SQL Server

Finding node order in XML document in SQL Server

You can emulate the position() function by counting the number of sibling nodes preceding each node:

SELECT
    code = value.value('@code', 'int'),
    parent_code = value.value('../@code', 'int'),
    ord = value.value('for $i in . return count(../*[. << $i]) + 1', 'int')
FROM @Xml.nodes('//value') AS T(value)

Here is the result set:

code   parent_code  ord
----   -----------  ---
1      NULL         1
11     1            1
111    11           1
12     1            2
121    12           1
1211   121          1
1212   121          2

How it works:

• The for $i in . clause defines a variable named $i that contains the current node (.). This is basically a hack to work around XQuery's lack of an XSLT-like current() function.
• The ../* expression selects all siblings (children of the parent) of the current node.
• The [. << $i] predicate filters the list of siblings to those that precede (<<) the current node ($i).
• We count() the number of preceding siblings and then add 1 to get the position. That way the first node (which has no preceding siblings) is assigned a position of 1.

Does the `nodes()` method keep the document order?

Yes, nodes() generates a row set in document order. The operator used in the query plan to do this is the Table Valued Function XML Reader.

Table-valued Function XML Reader inputs an XML BLOB as a parameter and
produces a row set representing XML nodes in XML document order. Other
input parameters may restrict XML nodes returned to a subset of XML
document.

But a query without order by has an undefined order so there are no guarantees.

One way to work around that is to use the id generated by the table valued function in row_number() over() clause and use the generated number in the order by.

select X.q
from
  (
  select T.c.query('.') as q,
         row_number() over(order by T.c) as rn
  from @xml.nodes('/Fruits/*') T(c)
  ) as X
order by X.rn

It is not possible to use T.c in an order by directly. Trying that will give you

Msg 493, Level 16, State 1, Line 19

The column 'c' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.

The error did not mention that it should work with row_number but it does and that could very well be bug that might get fixed so the code above will fail. But up until SQL Server 2012 it works just fine.

A way to get a guaranteed order without relying on the undocumented use of row_number would be to use a table of numbers where you extract the nodes by position.

select T.c.query('.') as q
from Numbers as N
  cross apply @xml.nodes('/Fruits/*[sql:column("N.Number")]') as T(c)
where N.Number between 1 and @xml.value('count(/Fruits/*)', 'int')
order by N.Number

Order of XML nodes from document preserved in insert?

There's no way to see it explicitly in an execution plan, but the id column returned by the nodes() method is a varbinary(900) OrdPath, which does encapsulate the original xml document order.

The solution offered by Mikael Eriksson on the related question Does the `nodes()` method keep the document order? relies on the OrdPath to provide an ORDER BY clause necessary to determine how identity values are assigned for the INSERT.

A slightly more compact usage follows:

CREATE TABLE #T 
(
    ID integer IDENTITY, 
    Fruit nvarchar(10) NOT NULL
);

DECLARE @xml xml = 
    N'
    <Fruits>
      <Apple />
      <Banana />
      <Orange />
      <Pear />
    </Fruits>
    ';

INSERT #T 
    (Fruit)
SELECT 
    N.n.value('local-name(.)', 'nvarchar(10)') 
FROM @xml.nodes('/Fruits/*') AS N (n)
ORDER BY
    ROW_NUMBER() OVER (ORDER BY N.n);

SELECT 
    T.ID, 
    T.Fruit 
FROM #T AS T
ORDER BY
    T.ID;

db<>fiddle

Using the OrdPath this way is presently undocumented, but the technique is sound in principle:

1. The OrdPath reflects document order.
2. The ROW_NUMBER computes sequence values ordered by OrdPath*.
3. The ORDER BY clause uses the row number sequence.
4. Identity values are assigned to rows as per the ORDER BY.

To be clear, this holds even if parallelism is employed. As Mikael says, the dubious aspect is using id in the ROW_NUMBER since id is not documented to be the OrdPath.

---

* The ordering is not shown in plans, but optimizer output using TF 8607 contains:

ScaOp_SeqFunc row_number order[CALC:QCOL: XML Reader with XPath filter.id ASC]

In SQL Server 2005, how can I store XML nodes in a table while preserving document order?

I'm currently doing the following, relying on the 'mp:id' to provide the ordering.

DECLARE @xmltext NVARCHAR(MAX)
SET @xmltext = 
N'
<Queue>
  <User name="Bob"/>
  <User name="Jane"/>
  <User name="Douglas"/>
  <User name="Samantha"/>
</Queue>';

DECLARE @xmlObjectHandler INT;
    -- Set up XML and the appropriate namespace(s) we'll be using
EXEC sp_xml_preparedocument @xmlObjectHandler OUTPUT, @xmltext;

DECLARE @TUserQueue TABLE (
    Ordinal INT IDENTITY(1,1),
    UserName NVARCHAR(200)
)

INSERT INTO @TUserQueue
SELECT 
    UserName
FROM
    OPENXML(@xmlObjectHandler, '/Queue/User')   
    WITH (
        NodeId INT '@mp:id',
        UserName NVARCHAR(200) '@name'
    ) AS users
ORDER BY users.NodeId;

SELECT * FROM @TUserQueue

SQL Server: Row_Number() order by sort by xml data order

Following the previous answer

declare @islem xml = '<?xml version="1.0"?>
<ArrayOfDecimal xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <decimal>4000000</decimal>
  <decimal>343</decimal>
  <decimal>4000000</decimal>
</ArrayOfDecimal>';

with Numbers(Number) as(
-- quick inline tally
 select 1 union all 
 select 2 union all 
 select 3 
)
 
select T.X.value('text()[1]', 'nvarchar(100)') as RowLabel,
       N.Number as RowNumber
from Numbers as N
cross apply @islem.nodes('ArrayOfDecimal/decimal[sql:column("N.Number")]') as T(X)
where N.Number between 1 and @islem.value('count(ArrayOfDecimal/decimal)', 'int');

You may wish to create a persistent Numbers tally table instead of quick sample in the query.

Order attribute names in xml using t-sql

Not pretty, but this is where my thinking takes me.

dbFiddle

Example

DECLARE @xml XML = N'
<tt>
    <cpost s="a" cena="0.0000" cpost_id="16385" flprt="1" moq="0" valuta_id="2" nmatr_id="14117" norg_id="1791" />
</tt>'

Declare @S varchar(max) = ''

Select @S = @S + concat(Item,'="',Value,'" ')
 From (
        Select Top 1000 
               Item   = attr.value('local-name(.)','varchar(100)')
              ,Value  = attr.value('.','varchar(max)') 
         From  @XML.nodes('/tt/cpost') as A(r)
         Cross Apply A.r.nodes('./@*') AS B(attr)
         Order By attr.value('local-name(.)','varchar(100)')
      ) A

Select convert(xml,'<tt><cpost '+@S+'/></tt>')

Returns

<tt>
  <cpost cena="0.0000" cpost_id="16385" flprt="1" moq="0" nmatr_id="14117" norg_id="1791" s="a" valuta_id="2" />
</tt>

EDIT - Added an In-Line Approach

DECLARE @xml XML = N'
<tt>
    <cpost s="a" cena="0.0000" cpost_id="16385" flprt="1" moq="0" valuta_id="2" nmatr_id="14117" norg_id="1791" />
</tt>'

Select convert(xml,'<tt><cpost '+Stuff((Select  ' ' +concat(Item,'="',Value,'" ')
 From (
        Select Top 1000 
               Item   = attr.value('local-name(.)','varchar(100)')
              ,Value  = attr.value('.','varchar(max)') 
         From  @XML.nodes('/tt/cpost') as A(r)
         Cross Apply A.r.nodes('./@*') AS B(attr)
         Order By attr.value('local-name(.)','varchar(100)')
      ) A
 For XML Path ('')),1,1,'') +'/></tt>')

SQL Server : FOR XML sorting control by attribute

If you want to reorder existing xml, you can use XQuery:

declare @data xml = '
<test>
    <tree abc="123"/>
    <tree abc="789"/>
    <tree-order abc="456"/>
</test>
'

select @data.query('<test>{for $i in test/* order by $i/@abc return $i}</test>')

Result:

<test>
  <tree abc="123" />
  <tree-order abc="456" />
  <tree abc="789" />
</test>

sql fiddle demo

update:

To reorder multiple nodes, you can use XQuery like this:

select @data.query('
    element Main {
        for $j in Main/test
            return element test {
                for $i in $j/* order by $i/@abc return $i
            }
    }
')

sql fiddle demo

update 2

To order by integer values of attributes, use cast as <type>:

select @data.query('
    element Main {
        for $j in Main/test
            return element test {
                for $i in $j/* order by $i/@abc cast as xs:integer?
                    return $i
            }
    }
')

sql fiddle demo

Use SQL Server to get all the data from XML nodes named the same

Try this :-

  SELECT 
      author.value('./text()[1]', 'varchar(MAX)') AS 'Author'
  FROM @MyXML.nodes('//refworks/reference/child::node()') AS ref(author)
  where  author.value('local-name(.)[1]', 'varchar(100)') ='a1'

child::node() represents an axis specifier which is child and :: is the axis separator.

For understanding child axis which is used to drill down in the node can be found in this MSDN document.

or manipulating xml data in sql server

Updated :-

A much simplier way You were on the right track .Specify the child node in the from clause for filtering the data

 SELECT 
      author.value('(.)[1]', 'varchar(MAX)') AS 'Author'
  FROM @MyXML.nodes('/refworks/reference/a1') AS ref(author)

SQL, Find node value in xml variable, if it exists insert additional nodes into xml variable

Check this:

declare @fields xml = '<F><ID>979</ID><ID>1000</ID><ID>989</ID></F>'
    , @add xml = '<ID>992</ID><ID>993</ID>'
;
if @fields.exist('/F[1]/ID[text()="1000"]') = 1
    set @fields.modify('insert sql:variable("@add") as last into /F[1]');

select @fields

How to query values from xml nodes?

SELECT  b.BatchID,
        x.XmlCol.value('(ReportHeader/OrganizationReportReferenceIdentifier)[1]','VARCHAR(100)') AS OrganizationReportReferenceIdentifier,
        x.XmlCol.value('(ReportHeader/OrganizationNumber)[1]','VARCHAR(100)') AS OrganizationNumber
FROM    Batches b
CROSS APPLY b.RawXml.nodes('/CasinoDisbursementReportXmlFile/CasinoDisbursementReport') x(XmlCol);

Demo: SQLFiddle

---

Related Topics