Traverse/Iterate in Nested element by XQuery

Question

I need to iterate in child element called Group.

DECLARE @XMLData XML = N'
  
    
      
      
        
        
      
      
      
      
      

    
    
      
      
    
  
  
    
      
      
            
            
     
    
  
';

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'
  
    
      
      
        
        
      
      
      
      
      

    
    
      
      
    
  
  
    
      
      
            
            
     
    
  
';

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'
    
        
            
            
                
                
            
            
                
            
            
        
        
            
            
        
    
    
        
            
            
                
                
            
        
    
';



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

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.

Yitzhak Khabinsky · Accepted Answer

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'
    
        
            
            
                
                
            
            
                
            
            
        
        
            
            
        
    
    
        
            
            
                
                
            
        
    
';

-- 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';

Traverse/Iterate in Nested element by XQuery

Sample code

EDIT

Answers (2)

Related Questions