Sort XML tag in correct order when shredding the XML in SQL Server

SQL006 0
Viewed 54

I have a table with a XML column there is no standard structure for the XML file as the XML tag can come multiple times or come at N number of nested levels. I am using an UDF query from this link How can I get a list of element names from an XML value in SQL Server. This UDF works great except the order in which the XML tag get inserted.

For this XML, it return the data based on alphabetical order

DECLARE @x XML  
SELECT @x = '
<books>
    <book id="101">
        <title>my book</title>
        <author>Myself</author>
    </book>
    <book id="202">
        <Writer>sample</Writer>
         <title>your book</title>      
         <author>you</author>       
    </book>
</books>'


;WITH cte AS 
( 
    SELECT 
        1 AS lvl,  
        x.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
        CAST(NULL AS NVARCHAR(MAX)) AS ParentName, 
        --CAST(1 AS INT) AS ParentPosition, 
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
        x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,  
        x.value('local-name(.)','NVARCHAR(MAX)')  
        + N'[' 
        + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)  
        + N']' AS XPath,  
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position, 
        --x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,  
        x.value('text()[1]','NVARCHAR(MAX)') AS Value,  
        x.query('.') AS CurrentNode,         
        --x.query('*') AS t,  
        CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,  
        CAST(1 AS INT) AS ID  
    FROM 
        @x.nodes('/*') a(x)  
    UNION ALL 
    SELECT 
        p.lvl + 1 AS lvl,  
        c.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
        CAST(p.Name AS NVARCHAR(MAX)) AS ParentName, 
        --CAST(p.Position AS INT) AS ParentPosition, 
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
        CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS FullPath,  
        CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)')+ N'['+ CAST(ROW_NUMBER() OVER (PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)') 
        ORDER BY (SELECT 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)) AS XPath,  
        ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
        ORDER BY (SELECT 1)) AS Position, 
        --CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS Tree,  
        CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, 
        c.query('.') AS CurrentNode,  
        --c.query('*') AS t,  
        CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ) AS Sort,  
        CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)  
    FROM 
        cte p  
    CROSS APPLY 
        CurrentNode.nodes('/*/*') b(c)
), cte2 AS 
(
    SELECT 
        lvl AS Depth,  
        Name AS NodeName,  
        ParentName, 
        --ParentPosition, 
        NodeType,  
        FullPath,  
        XPath,  
        Position, 
        --Tree AS TreeView,  
        Value,  
        CurrentNode AS XMLData,  
        Sort, ID  
    FROM 
        cte  
    UNION ALL 
    SELECT 
        p.lvl,  
        x.value('local-name(.)','NVARCHAR(MAX)'),  
        p.Name, 
        --p.Position, 
        CAST(N'Attribute' AS NVARCHAR(20)),  
        p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        1, 
        --SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)  
        --+ N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        x.value('.','NVARCHAR(MAX)'),  
        NULL,  
        p.Sort,  
        p.ID + 1  
    FROM 
        cte p  
    CROSS APPLY 
        CurrentNode.nodes('/*/@*') a(x)  
)  
SELECT 
    ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,  
    ParentName,-- ParentPosition,
    Depth, NodeName, Position,   
    NodeType, FullPath, XPath, --TreeView, 
    Value, XMLData 
FROM
    cte2

The result will order the (Id =4) tag before (ID =5) tag but in xml the tag order is different. Similarly the other XML block the tag come last instead of first.

ID  ParentName  Depth   NodeName    Position    NodeType    FullPath    XPath   Value   XMLData
    1   NULL    1   books   1   Element books   books[1]    NULL    <books><book id="101"><title>my book</title><author>Myself</author></book><book id="202"><Writer>sample</Writer><title>your book</title><author>you</author></book></books>
    2   books   2   book    1   Element books/book  books[1]/book[1]    NULL    <book id="101"><title>my book</title><author>Myself</author></book>
    3   book    2   id  1   Attribute   books/book/@id  books[1]/book[1]/@id    101 NULL
    4   book    3   author  1   Element books/book/author   books[1]/book[1]/author[1]  Myself  <author>Myself</author>
    5   book    3   title   1   Element books/book/title    books[1]/book[1]/title[1]   my book <title>my book</title>
    6   books   2   book    2   Element books/book  books[1]/book[2]    NULL    <book id="202"><Writer>sample</Writer><title>your book</title><author>you</author></book>
    7   book    2   id  1   Attribute   books/book/@id  books[1]/book[2]/@id    202 NULL
    8   book    3   author  1   Element books/book/author   books[1]/book[2]/author[1]  you <author>you</author>
    9   book    3   title   1   Element books/book/title    books[1]/book[2]/title[1]   your book   <title>your book</title>
    10  book    3   Writer  1   Element books/book/Writer   books[1]/book[2]/Writer[1]  sample  <Writer>sample</Writer>

But if comment the Xpath and Position field from the UDF it give us the correct order as shown below

   DECLARE @x XML  
SELECT @x = '
<books>
    <book id="101">
        <title>my book</title>
        <author>Myself</author>
    </book>
    <book id="202">
        <Writer>sample</Writer>
         <title>your book</title>      
         <author>you</author>       
    </book>
</books>'


;WITH cte AS (  
SELECT 
        1 AS lvl,  
        x.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
        CAST(NULL AS NVARCHAR(MAX)) AS ParentName, 
        --CAST(1 AS INT) AS ParentPosition, 
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
        x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,  
        --x.value('local-name(.)','NVARCHAR(MAX)')  
        --+ N'[' 
        --+ CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)  
        --+ N']' AS XPath,  
        --ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position, 
        --x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,  
        x.value('text()[1]','NVARCHAR(MAX)') AS Value,  
        x.query('.') AS CurrentNode,         
        --x.query('*') AS t,  
        CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,  
        CAST(1 AS INT) AS ID  
FROM @x.nodes('/*') a(x)  
UNION ALL 
SELECT 
        p.lvl + 1 AS lvl,  
        c.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
        CAST(p.Name AS NVARCHAR(MAX)) AS ParentName, 
    --CAST(p.Position AS INT) AS ParentPosition, 
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
        CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS FullPath,  
        --CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)')+ N'['+ CAST(ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)') 
        --ORDER BY (SELECT 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)) AS XPath,  
        --ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
        --ORDER BY (SELECT 1)) AS Position, 
        --CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS Tree,  
        CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, 
        c.query('.') AS CurrentNode,  
        --c.query('*') AS t,  
        CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ) AS Sort,  
        CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)  
FROM cte p  
CROSS APPLY CurrentNode.nodes('/*/*') b(c)) 
, cte2 AS (  
                                            SELECT 
                                            lvl AS Depth,  
                                            Name AS NodeName,  
                                            ParentName, 
                                            --ParentPosition, 
                                            NodeType,  
                                            FullPath,  
                                            --XPath,  
                                            --Position, 
                                            --Tree AS TreeView,  
                                            Value,  
                                            CurrentNode AS XMLData,  
                                            Sort, ID  
                                            FROM cte  
UNION ALL 
SELECT 
        p.lvl,  
        x.value('local-name(.)','NVARCHAR(MAX)'),  
        p.Name, 
        --p.Position, 
        CAST(N'Attribute' AS NVARCHAR(20)),  
        p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        --p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        --1, 
        --SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)  
        --+ N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        x.value('.','NVARCHAR(MAX)'),  
        NULL,  
        p.Sort,  
        p.ID + 1  
FROM cte p  
CROSS APPLY CurrentNode.nodes('/*/@*') a(x)  
)  
SELECT 
        ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,  
        ParentName,-- ParentPosition,
        Depth, NodeName, --Position,   
        NodeType, FullPath,-- XPath, --TreeView, 
        Value, XMLData 
FROM cte2

Below Result will display the correct order but it misses the XmlPath field which I need for further processing as it uniquely identify the element.

ID  ParentName  Depth   NodeName    NodeType    FullPath    Value   XMLData
1   NULL    1   books   Element books   NULL    <books><book id="101"><title>my book</title><author>Myself</author></book><book id="202"><Writer>sample</Writer><title>your book</title><author>you</author></book></books>
2   books   2   book    Element books/book  NULL    <book id="101"><title>my book</title><author>Myself</author></book>
3   book    2   id  Attribute   books/book/@id  101 NULL
4   book    3   title   Element books/book/title    my book <title>my book</title>
5   book    3   author  Element books/book/author   Myself  <author>Myself</author>
6   books   2   book    Element books/book  NULL    <book id="202"><Writer>sample</Writer><title>your book</title><author>you</author></book>
7   book    2   id  Attribute   books/book/@id  202 NULL
8   book    3   Writer  Element books/book/Writer   sample  <Writer>sample</Writer>
9   book    3   title   Element books/book/title    your book   <title>your book</title>
10  book    3   author  Element books/book/author   you <author>you</author>

Please can you help to sort the correct order with Xpath field.

Thanks

xquery sql-server-2019 sql-server
1 Answers

I just added a derived table to get the XPath after the sorting done in the inner query. Below is the query which sort the child nodes in correct order

DECLARE @x XML  
SELECT @x = '
<books>
    <book id="101">
        <title>my book</title>
        <author>Myself</author>
    </book>
    <book id="202">
        <Writer>sample</Writer>
         <title>your book</title>      
         <author>you</author>       
    </book>
</books>'


;WITH cte AS (  
SELECT 
        1 AS lvl,  
        x.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
        CAST(NULL AS NVARCHAR(MAX)) AS ParentName, 
        --CAST(1 AS INT) AS ParentPosition, 
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
        x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,  
        x.value('local-name(.)','NVARCHAR(MAX)')  
        + N'[' 
        + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)  
        + N']' AS XPath,  
        --ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position, 
        --x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,  
        x.value('text()[1]','NVARCHAR(MAX)') AS Value,  
        x.query('.') AS CurrentNode,         
        --x.query('*') AS t,  
        CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,  
        CAST(1 AS INT) AS ID  
FROM @x.nodes('/*') a(x)  
UNION ALL 
SELECT                      
lvl,  
Name,
ParentName,
NodeType,
FullPath, 
CAST(XPath + N'/'+ Name + N'['+ CAST(ROW_NUMBER() OVER(PARTITION BY Name 
ORDER BY (SELECT 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)) AS XPath,
Value, 
CurrentNode,
Sort, 
ID
FROM(

SELECT 
        p.lvl + 1 AS lvl,  
        c.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
        CAST(p.Name AS NVARCHAR(MAX)) AS ParentName, 
    --CAST(p.Position AS INT) AS ParentPosition, 
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
        CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS FullPath, 
        p.XPath AS XPath,
        --CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)')+ N'['+ CAST(ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)') ORDER BY (select 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)) AS XPath,  
        --ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
        --ORDER BY (SELECT 1)) AS Position, 
        --CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS Tree,  
        CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, 
        c.query('.') AS CurrentNode,  
        --c.query('*') AS t,  
        CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ) AS Sort,  
        CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)  AS ID
FROM cte p  
CROSS APPLY CurrentNode.nodes('/*/*') b(c))  AS D)
, cte2 AS (  
SELECT 
    lvl AS Depth,  
    Name AS NodeName,  
    ParentName, 
    --ParentPosition, 
    NodeType,  
    FullPath,  
    XPath,  
    --Position, 
    --Tree AS TreeView,  
    Value,  
    CurrentNode AS XMLData,  
    Sort, ID  
FROM cte  
UNION ALL 
SELECT 
        p.lvl,  
        x.value('local-name(.)','NVARCHAR(MAX)'),  
        p.Name, 
        --p.Position, 
        CAST(N'Attribute' AS NVARCHAR(20)),  
        p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        --1, 
        --SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)  
        --+ N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        x.value('.','NVARCHAR(MAX)'),  
        NULL,  
        p.Sort,  
        p.ID + 1  
FROM cte p  
CROSS APPLY CurrentNode.nodes('/*/@*') a(x)  
)  
SELECT 
        ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,  
        ParentName,-- ParentPosition,
        Depth, NodeName, --Position,   
        NodeType, FullPath, XPath, 
        --TreeView, 
        Value, XMLData 
FROM cte2
SQL0060
Related