0

My xml has 4 elements called Sheet, Group, Lineitem and BM

1) there could be multiple Sheet elements but sheet elements will not be nested.

2) Group will be child element of sheet element and Group can be nested means a group can have another child group or many nested child group.

3) Lineitem will be child element of Group element and Lineitem will not be nested.

2) BM will be child element of Lineitem element but BM element will not be nested.

i have a nested xml which i try to parse by xquery to insert data into table one by one. i store the nested element as Parent Child relationship in xml.

here is table structure

CREATE TABLE [dbo].[tblCSM_Details](
    [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
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Parent-child relation is maintained in table by the fields called ID and ParentID

My full code to parse nested element in xml.

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>
    <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" />
    </Group>
  </Sheet>
</Nodes>'

DECLARE @BMID INT,@ColLineItemID INT
DECLARE @SheetID INT,@GroupID INT,@LineItemID INT
DECLARE @SheetOrderID INT,@GroupOrderID INT,@LineItemOrderID INT,@BMOrderID INT

DECLARE @SheetStartIndex INT ,@SheetCount INT = @XMLData.value('count(/Nodes/Sheet)', 'INT');     
DECLARE @GroupStartIndex INT ,@GroupCount INT = @XMLData.value('count(/Nodes/Sheet/Group)', 'INT');   
DECLARE @LineitemStartIndex INT ,@LineitemCount INT = @XMLData.value('count(/Nodes/Sheet/Group/LineItem)', 'INT');   
DECLARE @BMStartIndex INT ,@BMCount INT = @XMLData.value('count(/Nodes/Sheet/Group/LineItem/BM)', 'INT');  

SET @SheetStartIndex = 1
SET @GroupStartIndex = 1
SET @LineitemStartIndex = 1
SET @BMStartIndex = 1

SET @SheetOrderID = 1
SET @GroupOrderID = 1
SET @LineItemOrderID = 1
SET @BMOrderID = 1

DECLARE @DisplayInCSM VARCHAR(MAX),
    @FontName VARCHAR(MAX),
    @FontStyle VARCHAR(MAX),
    @FontSize VARCHAR(MAX),
    @UnderLine VARCHAR(MAX),
    @BGColor VARCHAR(MAX)

SET @DisplayInCSM = ''
SET @FontName = ''
SET @FontStyle = ''
SET @FontSize = ''
SET @UnderLine = ''
SET @BGColor = ''

WHILE @SheetStartIndex <= @SheetCount      
BEGIN --Inserting sheet data 
    --PRINT 'Sheet'
    SELECT @DisplayInCSM = tab.col.value('@NodeText[1]', 'VARCHAR(MAX)'),
        @FontName = tab.col.value('@FontName[1]', 'VARCHAR(MAX)'),
        @FontStyle = tab.col.value('@FontStyle[1]', 'VARCHAR(MAX)'),
        @FontSize = tab.col.value('@FontSize[1]', 'VARCHAR(MAX)'),
        @UnderLine = tab.col.value('@UnderLine[1]', 'VARCHAR(MAX)'),
        @BGColor = tab.col.value('@BGColor[1]', 'VARCHAR(MAX)')
    FROM @XMLData.nodes('/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]') AS tab(col)

    INSERT INTO tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
    VALUES(1,0,'SHEET',@DisplayInCSM,@FontName,@FontStyle,@FontSize,@UnderLine,@BGColor,0,0,@SheetOrderID)

    SELECT @SheetID = SCOPE_IDENTITY()   

        BEGIN --Inserting Group data
            SET @GroupOrderID = 1
            SET @GroupStartIndex = 1
            WHILE @GroupStartIndex <= @GroupCount      
            BEGIN --Inserting Group data 
                SELECT @DisplayInCSM = tab.col.value('@NodeText[1]', 'VARCHAR(MAX)'),
                    @FontName = tab.col.value('@FontName[1]', 'VARCHAR(MAX)'),
                    @FontStyle = tab.col.value('@FontStyle[1]', 'VARCHAR(MAX)'),
                    @FontSize = tab.col.value('@FontSize[1]', 'VARCHAR(MAX)'),
                    @UnderLine = tab.col.value('@UnderLine[1]', 'VARCHAR(MAX)'),
                    @BGColor = tab.col.value('@BGColor[1]', 'VARCHAR(MAX)')
                FROM @XMLData.nodes('/Nodes/Sheet/Group[position() = sql:variable("@GroupStartIndex")]') AS tab(col)

                INSERT INTO tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
                VALUES(1,@SheetID,'GROUP',@DisplayInCSM,@FontName,@FontStyle,@FontSize,@UnderLine,@BGColor,0,0,@GroupOrderID)

                SELECT @GroupID = SCOPE_IDENTITY()   

                    BEGIN --Inserting LineItem data
                        SET @LineItemOrderID = 1
                        SET @LineitemStartIndex = 1
                        WHILE @LineitemStartIndex <= @LineitemCount      
                        BEGIN 
                            SELECT @DisplayInCSM = tab.col.value('@NodeText[1]', 'VARCHAR(MAX)'),
                                @FontName = tab.col.value('@FontName[1]', 'VARCHAR(MAX)'),
                                @FontStyle = tab.col.value('@FontStyle[1]', 'VARCHAR(MAX)'),
                                @FontSize = tab.col.value('@FontSize[1]', 'VARCHAR(MAX)'),
                                @UnderLine = tab.col.value('@UnderLine[1]', 'VARCHAR(MAX)'),
                                @BGColor = tab.col.value('@BGColor[1]', 'VARCHAR(MAX)'),
                                @ColLineItemID = CAST(tab.col.value('@LineItemID[1]', 'VARCHAR(MAX)') AS INT)
                            FROM @XMLData.nodes('/Nodes/Sheet/Group/LineItem[position() = sql:variable("@LineitemStartIndex")]') AS tab(col)

                            INSERT INTO tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
                            VALUES(1,@GroupID,'LINEITEM',@DisplayInCSM,@FontName,@FontStyle,@FontSize,@UnderLine,@BGColor,@ColLineItemID,0,@LineItemOrderID)

                                SELECT @LineItemID = SCOPE_IDENTITY()   

                                BEGIN --Inserting BM data
                                    SET @BMOrderID = 1 
                                    SET @BMStartIndex = 1
                                    WHILE @BMStartIndex <= @BMCount      
                                    BEGIN --Inserting sheet data 
                                        SELECT @DisplayInCSM = tab.col.value('@NodeText[1]', 'VARCHAR(MAX)'),
                                            @FontName = tab.col.value('@FontName[1]', 'VARCHAR(MAX)'),
                                            @FontStyle = tab.col.value('@FontStyle[1]', 'VARCHAR(MAX)'),
                                            @FontSize = tab.col.value('@FontSize[1]', 'VARCHAR(MAX)'),
                                            @UnderLine = tab.col.value('@UnderLine[1]', 'VARCHAR(MAX)'),
                                            @BGColor = tab.col.value('@BGColor[1]', 'VARCHAR(MAX)'),
                                            @BMID = CAST(tab.col.value('@BMID[1]', 'VARCHAR(MAX)') AS INT)

                                        FROM @XMLData.nodes('/Nodes/Sheet/Group/LineItem/BM[position() = sql:variable("@BMStartIndex")]') AS tab(col)

                                        INSERT INTO tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
                                        VALUES(1,@LineItemID,'BM',@DisplayInCSM,@FontName,@FontStyle,@FontSize,@UnderLine,@BGColor,0,@BMID,@BMOrderID)

                                        SET @BMOrderID = @BMOrderID + 1
                                        SET @BMStartIndex = @BMStartIndex + 1
                                    END
                                END

                                SET @LineItemOrderID = @LineItemOrderID + 1
                                SET @LineitemStartIndex = @LineitemStartIndex + 1
                        END
                    END                                 

                SET @GroupOrderID = @GroupOrderID + 1
                SET @GroupStartIndex = @GroupStartIndex + 1;      
            END 
        END

    SET @SheetOrderID = @SheetOrderID + 1
    SET @SheetStartIndex = @SheetStartIndex + 1;      
END

1) When run the above code then i saw 6 Group has been inserted into table. whereas i have 3 group in xml

2) BM element has been added under wrong lineitem.

i insert data into loop because i have parent child relation in table. so when i insert parent data then i store parent's inserted id into variable which i also insert during child data insertion.

i am not being able to understand where i made the mistake in code. anyone can help me. thanks

Indi_Rain
  • 179
  • 5
  • 17
  • Welcome to SO! You might take the [Tour](https://stackoverflow.com/tour) and you should (re-)read the help topic [How to Ask](https://stackoverflow.com/questions/how-to-ask) to get a feeling on how to contribute. You should provide a minimal, complete and reproducible example ([MCRE](https://stackoverflow.com/help/minimal-reproducible-example)) of your code as yours is hard to read. Keep in mind that creating an MCRE often helps to identify any problems by yourself and you will learn to better understand your code. – stackprotector Jun 10 '20 at 19:24
  • I already answered your question here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/acb262e7-9213-4249-bcd9-0a252b0ba3f8/facing-problem-to-parse-nested-xml-element-by-xquery?forum=transactsql – Yitzhak Khabinsky Jun 10 '20 at 23:00
  • @YitzhakKhabinsky thanks a lot for your help. – Indi_Rain Jun 11 '20 at 06:37

0 Answers0