Traverse/Iterate in Nested element by XQuery

Question

I need to iterate in child element called Group.

DECLARE @XMLData XML = N'<Nodes>
  <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Model1">
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Consensus Model">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1225" NodeText="Net Revenue" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1226" NodeText="Cost of Revenue">
        <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="01" NodeText="As % of Net Revenue" />
        <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="02" NodeText="Year over Year Growth" />
      </LineItem>
      <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Test Group1">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1225" NodeText="Test_Group_LI" />
      </Group>
      <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Test Group2"/>

    </Group>
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Segment Details">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1227" NodeText="Cost of Revenue-GAAP" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1228" NodeText="Gross Profit" />
    </Group>
  </Sheet>
  <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Model2">
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Key Financials">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1235" NodeText="Total Operating Expenses-GAAP" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1236" NodeText="EBITDA">
            <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="01" NodeText="BM_Test1" />
            <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="02" NodeText="BM_Test2" />
     </LineItem>
    </Group>
  </Sheet>
</Nodes>';

I posted this problem in MSDN forum and Mr Yitzhak Khabinsky answer there for my scenario. Which is working but when there is nested group then code is not inserting nested group data.

My XML will have Sheet, Group, Lineitem and BM element. Only group can be nested. A group can have multiple nested child group and those child group may have Lineitem or BM element.

So the hierarchy is:

Sheet may have multiple groups. sheet will not be nested.
Group can have lineitems and also group can have multiple child group.
lineitem will have BM element as child.

So only Group may have nested Nth child element.

Now here I am sharing a code which I got from MSDN forum which is working but when there is nested group then those nested group data is not getting inserted. My xquery knowledge is not good. I assume little change in code will make the code working for Nested Group.

Sample code

DECLARE @XMLData XML = N'<Nodes>
  <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Model1">
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Consensus Model">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1225" NodeText="Net Revenue" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1226" NodeText="Cost of Revenue">
        <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="01" NodeText="As % of Net Revenue" />
        <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="02" NodeText="Year over Year Growth" />
      </LineItem>
      <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Test Group1">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1225" NodeText="Test_Group_LI" />
      </Group>
      <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Test Group2"/>

    </Group>
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Segment Details">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1227" NodeText="Cost of Revenue-GAAP" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1228" NodeText="Gross Profit" />
    </Group>
  </Sheet>
  <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Model2">
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Key Financials">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1235" NodeText="Total Operating Expenses-GAAP" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1236" NodeText="EBITDA">
            <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="01" NodeText="BM_Test1" />
            <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="02" NodeText="BM_Test2" />
     </LineItem>
    </Group>
  </Sheet>
</Nodes>';

DECLARE @tblCSM_Details TABLE
(
    [CSM_ID] [int] NOT NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ParentID] [int] NULL,
    [Type] [varchar](30) NULL,
    [DisplayInCSM] [varchar](200) NULL,
    [FontName] [varchar](max) NULL,
    [FontStyle] [varchar](max) NULL,
    [FontSize] [varchar](max) NULL,
    [UnderLine] [varchar](max) NULL,
    [BGColor] [varchar](max) NULL,
    [LineItemID] [int] NULL,
    [BMID] [int] NULL,
    [ColOrder] [int] NULL
);


DECLARE @SheetID INT,@GroupID INT,@LineItemID INT, @BMID INT;

DECLARE @SheetStartIndex INT, @SheetCount INT;
DECLARE @GroupStartIndex INT, @GroupCount INT;   
DECLARE @LineitemStartIndex INT, @LineitemCount INT;   
DECLARE @BMStartIndex INT, @BMCount INT;

SET @SheetStartIndex = 1;
SET @SheetCount = @XMLData.value('count(/Nodes/Sheet)', 'INT');     
WHILE @SheetStartIndex <= @SheetCount BEGIN --Inserting sheet data 
    INSERT INTO @tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
    SELECT 1,0, c.value('local-name(.)','VARCHAR(30)') --'SHEET'
        , c.value('@NodeText', 'VARCHAR(MAX)')
        , c.value('@FontName', 'VARCHAR(MAX)')
        , c.value('@FontStyle', 'VARCHAR(MAX)')
        , c.value('@FontSize', 'VARCHAR(MAX)')
        , c.value('@UnderLine', 'VARCHAR(MAX)')
        , c.value('@BGColor', 'VARCHAR(MAX)')
        , 0,0, @SheetStartIndex
    FROM @XMLData.nodes('/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]') AS t(c);

    SELECT @SheetID = SCOPE_IDENTITY();   

    --Inserting Group data
    SET @GroupStartIndex = 1;
    SET @GroupCount = @XMLData.value('count(/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group)', 'INT');
    WHILE @GroupStartIndex <= @GroupCount BEGIN --Inserting Group data 
        INSERT INTO @tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
        SELECT 1,@SheetID, c.value('local-name(.)','VARCHAR(30)') --'GROUP'
            , c.value('@NodeText', 'VARCHAR(MAX)')
            , c.value('@FontName', 'VARCHAR(MAX)')
            , c.value('@FontStyle', 'VARCHAR(MAX)')
            , c.value('@FontSize', 'VARCHAR(MAX)')
            , c.value('@UnderLine', 'VARCHAR(MAX)')
            , c.value('@BGColor', 'VARCHAR(MAX)')
            , 0,0, @GroupStartIndex
        FROM @XMLData.nodes('/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group[position() = sql:variable("@GroupStartIndex")]') AS t(c);

        SELECT @GroupID = SCOPE_IDENTITY();   

        --Inserting LineItem data
        SET @LineitemStartIndex = 1;
        SET @LineitemCount = @XMLData.value('count(/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group[position() = sql:variable("@GroupStartIndex")]/LineItem)', 'INT');
        WHILE @LineitemStartIndex <= @LineitemCount BEGIN 
            INSERT INTO @tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
            SELECT 1,@GroupID, c.value('local-name(.)','VARCHAR(30)') --'LINEITEM'
                , c.value('@NodeText', 'VARCHAR(MAX)')
                , c.value('@FontName', 'VARCHAR(MAX)')
                , c.value('@FontStyle', 'VARCHAR(MAX)')
                , c.value('@FontSize', 'VARCHAR(MAX)')
                , c.value('@UnderLine', 'VARCHAR(MAX)')
                , c.value('@BGColor', 'VARCHAR(MAX)')
                , c.value('@LineItemID', 'INT')
                , 0, @LineitemStartIndex
            FROM @XMLData.nodes('/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group[position() = sql:variable("@GroupStartIndex")]/LineItem[position() = sql:variable("@LineitemStartIndex")]') AS t(c);

            SELECT @LineItemID = SCOPE_IDENTITY();

            --Inserting BM data
            SET @BMStartIndex = 1;
            SET @BMCount = @XMLData.value('count(/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group[position() = sql:variable("@GroupStartIndex")]/LineItem[position() = sql:variable("@LineitemStartIndex")]/BM)', 'INT');
            WHILE @BMStartIndex <= @BMCount BEGIN --Inserting sheet data 
                INSERT INTO @tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
                SELECT 1,@LineItemID, c.value('local-name(.)','VARCHAR(30)') --BM
                    , c.value('@NodeText', 'VARCHAR(MAX)')
                    , c.value('@FontName', 'VARCHAR(MAX)')
                    , c.value('@FontStyle', 'VARCHAR(MAX)')
                    , c.value('@FontSize', 'VARCHAR(MAX)')
                    , c.value('@UnderLine', 'VARCHAR(MAX)')
                    , c.value('@BGColor', 'VARCHAR(MAX)')
                    , 0
                    , c.value('@BMID', 'INT')
                    , @BMStartIndex
                FROM @XMLData.nodes('/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group[position() = sql:variable("@GroupStartIndex")]/LineItem[position() = sql:variable("@LineitemStartIndex")]/BM[position() = sql:variable("@BMStartIndex")]') AS t(c);

                SET @BMStartIndex += 1;
            END;

            SET @LineitemStartIndex += 1;
        END;

        SET @GroupStartIndex += 1;      
    END;

    SET @SheetStartIndex += 1;      
END;

SELECT * FROM @tblCSM_Details;

Please some one tell me what to change in code as a result it should work to handle nested group element.

EDIT

DECLARE @XMLData XML = 
N'<Nodes>
    <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
           NodeText="Model1">
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Consensus Model">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1225" NodeText="Net Revenue"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1226" NodeText="Cost of Revenue">
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="01" NodeText="As % of Net Revenue"/>
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="02" NodeText="Year over Year Growth"/>
            </LineItem>
            <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
                   BGColor="" NodeText="Test Group1">
                <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                          BGColor="" LineItemID="1225" NodeText="Test_Group_LI"/>
            </Group>
            <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
                   BGColor="" NodeText="Test Group2"/>
        </Group>
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Segment Details">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1227"
                      NodeText="Cost of Revenue-GAAP"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1228" NodeText="Gross Profit"/>
        </Group>
    </Sheet>
    <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
           NodeText="Model2">
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Key Financials">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1235"
                      NodeText="Total Operating Expenses-GAAP"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1236" NodeText="EBITDA">
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="01" NodeText="BM_Test1"/>
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="02" NodeText="BM_Test2"/>
            </LineItem>
        </Group>
    </Sheet>
</Nodes>';



WITH Sheets AS
(
    SELECT 'Sheet' AS [Type]
          ,ROW_NUMBER() OVER(ORDER BY A.sh) AS Id
          ,REPLACE(STR(ROW_NUMBER() OVER(ORDER BY A.sh),2),' ','0') AS SortString
          ,sh.query('.') Content
    FROM @XMLData.nodes('/Nodes/Sheet') A(sh)
)
,SheetGroups AS
(
    SELECT sh.* FROM Sheets sh
    UNION ALL
    SELECT 'Group'
          ,ROW_NUMBER() OVER(PARTITION BY sh.SortString ORDER BY A.gr)
          ,CONCAT(sh.SortString,REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY sh.SortString ORDER BY A.gr),2),' ','0')) 
          ,gr.query('.') 
    FROM Sheets sh
    OUTER APPLY Content.nodes('Sheet/Group') A(gr)
)
,LineItems AS
(
    SELECT shgr.* FROM SheetGroups shgr
    UNION ALL
    SELECT 'LineItem'
          ,ROW_NUMBER() OVER(PARTITION BY shgr.SortString ORDER BY A.li)
          ,CONCAT(shgr.SortString,REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY shgr.SortString ORDER BY A.li),2),' ','0')) 
          ,li.query('.') 
    FROM SheetGroups shgr
    OUTER APPLY Content.nodes('Group/LineItem') A(li)
)
,BMs AS
(
    SELECT li.* FROM LineItems li
    UNION ALL
    SELECT 'BM'
          ,ROW_NUMBER() OVER(PARTITION BY li.SortString ORDER BY A.bm)
          ,CONCAT(li.SortString,REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY li.SortString ORDER BY A.bm),2),' ','0')) 
          ,bm.query('.') 
    FROM LineItems li
    OUTER APPLY Content.nodes('LineItem/BM') A(bm)
)
,FinalList AS
(
    SELECT 1 AS CSM_ID
          ,ROW_NUMBER() OVER(ORDER BY BMs.SortString) AS ID
          ,BMs.SortString
          ,BMs.[Type]
          ,BMs.Content.value('(/*/@NodeText)[1]','varchar(max)') As DisplayInCSM
          ,BMs.Content.value('(/*/@FontName)[1]','varchar(max)') As FontName
          ,BMs.Content.value('(/*/@FontStyle)[1]','varchar(max)') AS FontStyle
          ,BMs.Content.value('(/*/@FontSize)[1]','varchar(max)') AS FontSize
          ,BMs.Content.value('(/*/@UnderLine)[1]','varchar(max)') AS UnderLine
          ,BMs.Content.value('(/*/@BGColor)[1]','varchar(max)') AS BGColor
          ,BMs.Content.value('(/*/@LineItemID)[1]','varchar(max)') AS LineItemID
          ,BMs.Content.value('(/*/@BMID)[1]','varchar(max)') AS BMID
          ,BMs.Id AS ColOrder
    FROM BMs
    WHERE Content IS NOT NULL
)
SELECT f1.CSM_ID
      ,f1.ID
      ,(SELECT f2.ID FROM FinalList f2 WHERE f2.SortString=LEFT(f1.SortString,LEN(f1.SortString)-2))
      ,f1.[Type]
      ,f1.DisplayInCSM
      ,f1.FontName
      ,f1.FontStyle
      ,f1.FontSize
      ,f1.UnderLine
      ,f1.BGColor
      ,f1.LineItemID
      ,f1.BMID
      ,f1.ColOrder
FROM FinalList f1
ORDER BY SortString;

--select * from Sheets

@Shnugo when i test your code with nested group then it could not consider those nested group. there is nested group in xml called Group1 & Group2

    <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
           BGColor="" NodeText="Test Group1">
        <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                  BGColor="" LineItemID="1225" NodeText="Test_Group_LI"/>
    </Group>
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
           BGColor="" NodeText="Test Group2"/>

A Group can have a as many as nested group and each nested group can have lineitem or not and each lineitem can have BM element or not.

How important is the ParentID column to you? It could be done in a single statement (without all the while loops) if ParentID is not required. — AlwaysLearning, Jun 11 '20 at 11:57
You might have a look [here](https://stackoverflow.com/q/61886650/5089204). There are two approaches to read *the whole and everything* out of a XML. The outdated `FROM OPENXML` approach has some parallels with your needs. But I doubt, if this is the right goal... — Shnugo, Jun 11 '20 at 12:06
@Shnugo basically i have to iterate in nested xml element and insert data one by one. yes i will have a look the url you mentioned. thanks — Indi_Rain, Jun 11 '20 at 12:29

Yitzhak Khabinsky · Accepted Answer · 2020-06-12T11:45:46.023

Here is another way to handle it. No loops.

The method is using a recursive CTE. This way any element could be anywhere. You can uncomment the last WHERE clause to get even attributes.

All credit goes to the folks at this link: How can I get a list of element names from an XML value in SQL Server

SQL

DECLARE @xml XML = 
N'<Nodes>
    <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
           NodeText="Model1">
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Consensus Model">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1225" NodeText="Net Revenue"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1226" NodeText="Cost of Revenue">
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="01" NodeText="As % of Net Revenue"/>
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="02" NodeText="Year over Year Growth"/>
            </LineItem>
            <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
                   BGColor="" NodeText="Test Group1">
                <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                          BGColor="" LineItemID="1225" NodeText="Test_Group_LI"/>
            </Group>
            <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
                   BGColor="" NodeText="Test Group2"/>
        </Group>
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Segment Details">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1227"
                      NodeText="Cost of Revenue-GAAP"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1228" NodeText="Gross Profit"/>
        </Group>
    </Sheet>
    <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
           NodeText="Model2">
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Key Financials">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1235"
                      NodeText="Total Operating Expenses-GAAP"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1236" NodeText="EBITDA">
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="01" NodeText="BM_Test1"/>
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="02" NodeText="BM_Test2"/>
            </LineItem>
        </Group>
    </Sheet>
</Nodes>';

-- Solution # 2.
-- a thing of beauty.
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 this,         
        x.query('*') AS t,  
        CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,  
        CAST(1 AS INT) AS ID  
FROM @xml.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 this,  
        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 p.t.nodes('*') b(c))
, cte2 AS (  
SELECT lvl AS Depth,  
        Name AS NodeName,  
        ParentName, 
        ParentPosition, 
        NodeType,  
        FullPath,  
        XPath,  
        Position, 
        Tree AS TreeView,  
        Value,  
        this 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 this.nodes('/*/@*') a(x)  
)  
SELECT ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,  
    ParentName, ParentPosition,Depth, NodeName, Position,   
    NodeType, FullPath, XPath, TreeView, Value, XMLData
    , XMLData.value('*[1]/@NodeText','VARCHAR(30)') AS NodeText
    , XMLData.value('*[1]/@FontName','VARCHAR(30)') AS FontName
    , XMLData.value('*[1]/@FontStyle','VARCHAR(30)') AS FontStyle
FROM cte2
WHERE cte2.NodeType = 'Element';

good answer but i feel very hard to understand. how to use above code to loop in XML hierarchy? — Indi_Rain, Jun 12 '20 at 07:04
I execute your code which return data in tabular format. How to read values of FontName and FontStyle for each record ? give me idea. — Indi_Rain, Jun 12 '20 at 07:08
Hi Yitzhak, I think, this can be solved with little less string gymnastics :-) — Shnugo, Jun 12 '20 at 08:13
@TridipBhattacharjee, i updated the answer by adding three attributes retrieval. The same way you can get to any attribute. — Yitzhak Khabinsky, Jun 12 '20 at 11:47
Thanks for sharing this solution I have used it but the only issue I found is it's not correctly inserting the tag in a correct order because of Xpath, Position fields Order by clause as the sql server order the tags in alphabetical order not based on xml tag order. for example in below sample comes first before .... <books> <book id="101"> <title>my book Myself sample your book you ' — SQL006, Sep 18 '22 at 16:06

score 0 · Answer 2 · answered Jun 12 '20 at 07:57

Here is one more approach, which might be a bit more straight forward. Also no loops...

WITH Sheets AS
(
    SELECT 'Sheet' AS [Type]
          ,ROW_NUMBER() OVER(ORDER BY A.sh) AS Id
          ,REPLACE(STR(ROW_NUMBER() OVER(ORDER BY A.sh),2),' ','0') AS SortString
          ,sh.query('.') Content
    FROM @XMLData.nodes('/Nodes/Sheet') A(sh)
)
,SheetGroups AS
(
    SELECT sh.* FROM Sheets sh
    UNION ALL
    SELECT 'Group'
          ,ROW_NUMBER() OVER(PARTITION BY sh.SortString ORDER BY A.gr)
          ,CONCAT(sh.SortString,REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY sh.SortString ORDER BY A.gr),2),' ','0')) 
          ,gr.query('.') 
    FROM Sheets sh
    OUTER APPLY Content.nodes('Sheet/Group') A(gr)
)
,LineItems AS
(
    SELECT shgr.* FROM SheetGroups shgr
    UNION ALL
    SELECT 'LineItem'
          ,ROW_NUMBER() OVER(PARTITION BY shgr.SortString ORDER BY A.li)
          ,CONCAT(shgr.SortString,REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY shgr.SortString ORDER BY A.li),2),' ','0')) 
          ,li.query('.') 
    FROM SheetGroups shgr
    OUTER APPLY Content.nodes('Group/LineItem') A(li)
)
,BMs AS
(
    SELECT li.* FROM LineItems li
    UNION ALL
    SELECT 'BM'
          ,ROW_NUMBER() OVER(PARTITION BY li.SortString ORDER BY A.bm)
          ,CONCAT(li.SortString,REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY li.SortString ORDER BY A.bm),2),' ','0')) 
          ,bm.query('.') 
    FROM LineItems li
    OUTER APPLY Content.nodes('LineItem/BM') A(bm)
)
,FinalList AS
(
    SELECT 1 AS CSM_ID
          ,ROW_NUMBER() OVER(ORDER BY BMs.SortString) AS ID
          ,BMs.SortString
          ,BMs.[Type]
          ,BMs.Content.value('(/*/@NodeText)[1]','varchar(max)') As DisplayInCSM
          ,BMs.Content.value('(/*/@FontName)[1]','varchar(max)') As FontName
          ,BMs.Content.value('(/*/@FontStyle)[1]','varchar(max)') AS FontStyle
          ,BMs.Content.value('(/*/@FontSize)[1]','varchar(max)') AS FontSize
          ,BMs.Content.value('(/*/@UnderLine)[1]','varchar(max)') AS UnderLine
          ,BMs.Content.value('(/*/@BGColor)[1]','varchar(max)') AS BGColor
          ,BMs.Content.value('(/*/@LineItemID)[1]','varchar(max)') AS LineItemID
          ,BMs.Content.value('(/*/@BMID)[1]','varchar(max)') AS BMID
          ,BMs.Id AS ColOrder
    FROM BMs
    WHERE Content IS NOT NULL
)
SELECT f1.CSM_ID
      ,f1.ID
      ,(SELECT f2.ID FROM FinalList f2 WHERE f2.SortString=LEFT(f1.SortString,LEN(f1.SortString)-2))
      ,f1.[Type]
      ,f1.DisplayInCSM
      ,f1.FontName
      ,f1.FontStyle
      ,f1.FontSize
      ,f1.UnderLine
      ,f1.BGColor
      ,f1.LineItemID
      ,f1.BMID
      ,f1.ColOrder
FROM FinalList f1
ORDER BY SortString;

The result is as provided by you.

The idea in short:

It follows a similar idea as Yitzhak's answer (Unioning the results), but uses several CTEs to keep it easier to read and avoids complex joining.
Each CTE can provide its own partitioned ID
A SortString is concatenated (use more digits if you expect more than 99 nested children of one type)
The parent's id is finally taken by joining to the SortString, hence finding the parent.

@TridipBhattacharjee Might be I get this wrong, but the given code will read these attributes from any element. Did you try this? — Shnugo, Jun 12 '20 at 08:38
i am weak in xquery but i like to know that why i need to read attribute value this way...`BMs.Content.value('(/*/@FontName)[1]','varchar(max)') As FontName` before i was reading attribute like this way `c.value('@FontName', 'VARCHAR(MAX)')` please guide me further. — Indi_Rain, Jun 12 '20 at 08:40
@TridipBhattacharjee You might include `Content` into the list of returned values (just take away `.value()`). Any element has a different name, hence we can use `*`. Within this anonymus element we can fetch the attribute. Attributes are named in XQuery with a leading `@`. Finally we embrace the whole XPath with `(...)[1]`, which means: Take the very first occurance. The method `.value()` demands for a guaranteed singleton value. — Shnugo, Jun 12 '20 at 08:48
when i test your code with nested group then it could not read/consider those nested group. there is nested group in xml called **Group1 & Group2** A Group can have a as many as nested group and each nested group can have lineitem or not and each lineitem can have BM element or not. please see my Edit. — Indi_Rain, Jun 12 '20 at 08:56
Mr Yitzhak Khabinsky posted a code here which is bit harder but that can read nested group. so my request that can you please run your code with the xml i mention in edit section then you can see your code not reading nested group in xml. please suggest me a better way because in my xml group can be nested in Nth level and some time nested group will have child lineitem elements or may not. please look into this issue. thanks — Indi_Rain, Jun 12 '20 at 09:10

Traverse/Iterate in Nested element by XQuery

Sample code

EDIT

2 Answers2