Запрос XML с вложенными узлами в 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>

Сначала я помещаю значения первого уровня (FOLDER) во временную таблицу с именем @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)

Внутри курсора я вставляю значения второго уровня (DATA) с помощью 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)

До этого момента все работает корректно. Теперь мне нужно получить значения с третьего уровня (OPTION) и вставить их в другую временную таблицу с именем @tmpOptions. Я попытался добавить еще один CROSS APPLY, но безуспешно.

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)

Я не получаю никаких ошибок, поэтому я не уверен, что я делаю неправильно.


xml sql-server-2008 cross-apply cursor
person Fabio L.    schedule 13.10.2017    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Код, который вы разместили, неверен...

Я не получаю никаких ошибок, поэтому я не уверен, что я делаю неправильно.

Слишком много закрывающих скобок, запятая отсутствует, и вы используете зарезервированные слова, которые следует заключать в кавычки, например [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 для вставки каждого набора данных вместе с соответствующим внешним ключом в свою таблицу.

person Shnugo    schedule 20.10.2017
comment
Синтаксическая ошибка отсутствует в моем исходном XML-файле (то, что я опубликовал, - это просто упрощенная версия, которую я написал на месте). Позже я узнал, что то, что я написал, действительно работает, я сделал ошибку копирования/вставки в предложении where. В любом случае, я очень заинтересован в том, что вы предложили. Я дам вам знать, как только попробую. - person Fabio L.; 23.10.2017
comment
Я только что проверил, и это работает как шарм. Намного проще и быстрее, чем метод, который я использовал. Благодарю вас! - person Fabio L.; 27.10.2017