交叉应用时查询具有嵌套节点的XML [英] Query XML with nested nodes on Cross Apply

问题描述

给定如下结构的XML：

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

首先，我使用

将第一级(文件夹)的值放入名为@tmpFolders的临时表中

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

然后我在@tmpFolders上声明了一个游标

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

在游标内部，我使用CROSS APPLY将第二级(数据)的值插入另一个名为@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)

到目前为止，一切都运行正常。 现在，我需要从第三级(选项)获取值，并将它们插入到另一个名为@tmpOptions的临时表中 我尝试添加另一个交叉应用，但未成功

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)

我没有收到任何错误，所以我不确定我做错了什么。

推荐答案

您发布的代码不正确.

我没有收到任何错误，所以我不确定我做错了什么。

有一些右括号太多，缺少逗号，并且您使用的是保留字，应该像[option]那样计算。这必须引发错误.

这样试试

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)

但是.

如果有多个<FOLDER>或多个<DATA>，则您的代码-可能！-没有执行预期的操作。在CURSOR中，您将不带任何过滤的所有元素读取给给定父对象.

无论如何，这不是您应该这样做的方式。尽可能避免CURSOR！

您的最终目标是什么？如果要在相关表中传输此结构，请执行以下操作。是否有意让这两个选项的代码相同(200)？可能是复制粘贴错误.如果所有内部代码都是唯一的，就很容易做到：

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

如果内部编码不唯一，可以这样做：

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

这适用于任意数量的文件夹、嵌套数据和嵌套选项.

将此内容写入临时表，然后使用SELECT DISTINCT将每组数据连同相应的外键一起插入到其表中。

这篇关于交叉应用时查询具有嵌套节点的XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！