Query XML with nested nodes on Cross Apply

Question

Given a XML structured like this:

<ROOT_NODE>
    <FOLDER_LIST>
        <FOLDER>
            <CODE_FOLDER>1</CODE_FOLDER>
            <DESCRIPTION>This is a folder</DESCRIPTION>
            <DATA_LIST>
                <DATA>
                    <CODE_DATA>100</CODE_DATA>
                    <OPTIONS>
                        <OPTION>
                            <CODE_OPTION>200</CODE_OPTION>
                            <PRINT_TEXT>This is a test</PRINT_TEXT>
                        </OPTION>
                        <OPTION>
                            <CODE_OPTION>200</CODE_OPTION>
                            <PRINT_TEXT>This is a test</PRINT_TEXT>
                        </OPTION>
                    </OPTIONS>
                </DATA>
            </DATA_LIST>
        </FOLDER>
    </FOLDER_LIST>
</ROOT_NODE>

First I put the values of the first level (FOLDER) inside a temporary table called @tmpFolders using

FROM @xml.nodes('ROOT_NODE/FOLDER_LIST/FOLDER') as folder(id)

Then I declared a cursor on @tmpFolders

DECLARE cur CURSOR FOR
SELECT CODE_FOLDER, DESCRIPTION FROM @tmpFolders 
OPEN cur 
FETCH NEXT FROM cur INTO @codeFolder, @description
WHILE (@@FETCH_STATUS = 0)

Inside the cursor I insert the values of the second level (DATA) using CROSS APPLY into another temporary table called @tmpData

INSERT INTO @tmpData(CODE_DATA)
SELECT data.id.value('CODE_DATA[1]','INT'))
FROM @xml.nodes('ROOT_NODE/FOLDER_LIST/FOLDER') as folder(Id)
    CROSS APPLY folder.Id.nodes('DATA_LIST/DATA') as data(Id)

Up to this point, everything works correctly. Now I need the get the values from the third level (OPTION) and insert them into another temporary table called @tmpOptions I tried adding another CROSS APPLY but without success

INSERT INTO @tmpOptions(CODE_OPTION, PRINT_TEXT)
SELECT data.id.value('CODE_DATA[1]','INT')),
       option.id.value('CODE_OPTION[1]','INT'))
       option.id.value('PRINT_TEXT[1]','VARCHAR(50)'))
FROM @xml.nodes('ROOT_NODE/FOLDER_LIST/FOLDER') as folder(Id)
    CROSS APPLY folder.Id.nodes('DATA_LIST/DATA') as data(Id)
    CROSS APPLY data.Id.nodes('OPTIONS/OPTION') as option(Id)

I don't get any errors, so I'm not sure what I'm doing wrong.

score 2 · Accepted Answer · answered Oct 20 '17 at 06:58

The code you posted is not correct...

I don't get any errors, so I'm not sure what I'm doing wrong.

There are some closing brackets to much, a comma is missing and you are using reserved words, which should be qouted like [option]. This must throw errors...

Try it like this

SELECT [data].id.value('CODE_DATA[1]','INT'),
       [option].id.value('CODE_OPTION[1]','INT'),
       [option].id.value('PRINT_TEXT[1]','VARCHAR(50)')
FROM @xml.nodes('ROOT_NODE/FOLDER_LIST/FOLDER') as folder(Id)
    CROSS APPLY folder.Id.nodes('DATA_LIST/DATA') as [data](Id)
    CROSS APPLY [data].Id.nodes('OPTIONS/OPTION') as [option](Id)

BUT ...

Your code is - probably! - not doing what you'd expect if there is more than one <FOLDER> or more than one <DATA>. Within your CURSOR you read all elements without any filter to the given parent...

Anyway, this is not the way you should do that. Avoid CURSOR wherever you can!

What is your final goal? If you want to transfer this structure in related tables. Is it on purpose, that the option's code is the same (200) for both? Might be a copy'n'paste error... If all internal codes were unique it was as easy as:

SELECT Fld.value(N'(CODE_FOLDER/text())[1]',N'int') AS Folder_Code
      ,Fld.value(N'(DESCRIPTION/text())[1]',N'nvarchar(max)') AS Folder_Description
      ,Dt.value(N'(CODE_DATA/text())[1]',N'int') AS Data_Code
      ,Opt.value(N'(CODE_OPTION/text())[1]',N'int') AS Option_Code
      ,Opt.value(N'(PRINT_TEXT/text())[1]',N'nvarchar(max)') AS Option_Text
      --Generate running IDs, you might add an existing max id if you have to insert into filled tables
      ,DENSE_RANK() OVER(ORDER BY Fld.value(N'(CODE_FOLDER/text())[1]',N'int')) AS FolderId
      ,DENSE_RANK() OVER(ORDER BY Fld.value(N'(CODE_FOLDER/text())[1]',N'int')
                                 ,Dt.value(N'(CODE_DATA/text())[1]',N'int')) AS DataId
      ,DENSE_RANK() OVER(ORDER BY Fld.value(N'(CODE_FOLDER/text())[1]',N'int')
                                 ,Dt.value(N'(CODE_DATA/text())[1]',N'int')
                                 ,Opt.value(N'(CODE_OPTION/text())[1]',N'int')) AS OptionId
FROM @xml.nodes(N'/ROOT_NODE/FOLDER_LIST/FOLDER') AS A(Fld)
OUTER APPLY Fld.nodes(N'DATA_LIST/DATA') AS B(Dt)
OUTER APPLY Dt.nodes(N'OPTIONS/OPTION') AS C(Opt);

If the internal codes are not unique, you can go this way:

WITH Folders AS
(
    SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS FolderId
           ,Fld.value(N'(CODE_FOLDER/text())[1]',N'int') AS Folder_Code
           ,Fld.value(N'(DESCRIPTION/text())[1]',N'nvarchar(max)') AS Folder_Description
           ,Fld.query(N'DATA_LIST/DATA') AS Node_data
    FROM @xml.nodes(N'/ROOT_NODE/FOLDER_LIST/FOLDER') AS A(Fld)
)
,FoldersWithDatas AS
(
    SELECT Folders.FolderId
          ,Folders.Folder_Code
          ,Folders.Folder_Description
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS DataId
          ,Dt.value(N'(CODE_DATA/text())[1]',N'int') AS Data_Code
          ,Dt.query(N'OPTIONS/OPTION') AS Node_data
    FROM Folders
    OUTER APPLY Folders.Node_data.nodes(N'DATA') AS A(Dt)
)
SELECT   FoldersWithDatas.FolderId
        ,FoldersWithDatas.Folder_Code
        ,FoldersWithDatas.Folder_Description
        ,FoldersWithDatas.DataId
        ,FoldersWithDatas.Data_Code
        ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS OptionId
        ,Dt.value(N'(CODE_OPTION/text())[1]',N'int') AS Option_Code
FROM FoldersWithDatas
OUTER APPLY FoldersWithDatas.Node_data.nodes(N'OPTION') AS A(Dt);

This would work with any number of folder, nested Datas and nested options...

Write this into a temp table and use SELECT DISTINCT to insert each set of data together with the appropriate foreign key into its table.

The syntax error are not present in my original xml file (what I posted is just a simplified version that I wrote on the spot). I later found out that the what I wrote actually works, I made a copy/paste mistake in the where clause. Anyway I'm quite interested in what you suggested. I will let you know as soon as I try it. — Fabio L., Oct 23 '17 at 17:33
I have just tested and it works like a charm. A lot easier and faster than the method I was using. Thank you! — Fabio L., Oct 27 '17 at 15:23

Query XML with nested nodes on Cross Apply

1 Answers1

BUT ...

Linked