SQL Server из параметра XML в таблицу - работа с необязательными дочерними узлами

ProgramBox

SQL Server из параметра XML в таблицу — работа с необязательными дочерними узлами

Post author:admin
Запись опубликована:30 августа, 2022
Post category:Вопросы по программированию

#sql-server #xml #sql-server-2008 #sql-server-2008-r2

#sql-сервер #xml #sql-server-2008 #sql-server-2008-r2

Вопрос:

На SQL Server 2008 R2 я пытаюсь прочитать значение XML в виде таблицы.

Пока я здесь :

 DECLARE @XMLValue AS XML;
SET @XMLValue = '<SearchQuery>
    <ResortID>1453</ResortID>
    <CheckInDate>2011-10-27</CheckInDate>
    <CheckOutDate>2011-11-04</CheckOutDate>
    <Room>
        <NumberOfADT>2</NumberOfADT>
        <CHD>
            <Age>10</Age>
        </CHD>
        <CHD>
            <Age>12</Age>
        </CHD>
    </Room>
    <Room>
        <NumberOfADT>1</NumberOfADT>
    </Room>
    <Room>
        <NumberOfADT>1</NumberOfADT>
        <CHD>
            <Age>7</Age>
        </CHD>
    </Room>
</SearchQuery>';

SELECT 
    Room.value('(NumberOfADT)[1]', 'INT') AS NumberOfADT
FROM @XMLValue.nodes('/SearchQuery/Room') AS SearchQuery(Room);

Как вы можете видеть, Room узел иногда получает CHD дочерние узлы, но иногда нет.

Предположим, что я получаю это значение XML в качестве параметра хранимой процедуры. Итак, мне нужно поработать со значениями, чтобы запросить мои таблицы базы данных. Каков наилучший способ полностью прочитать этот параметр XML?

Редактировать

Я думаю, мне нужно выразить здесь то, чего я ожидаю взамен. Приведенный ниже код скрипта предназначен для таблицы, которая мне здесь нужна :

 DECLARE @table AS TABLE(
    ResorrtID INT,
    CheckInDate DATE,
    CheckOutDate DATE,
    NumberOfADT INT,
    CHDCount INT,
    CHDAges NVARCHAR(100)
);

Для значения XML, которое я привел выше, подходит приведенная ниже вставка t-sql :

 INSERT INTO @table VALUES(1453, '2011-10-27', '2011-11-04', 2, 2, '10;12');
INSERT INTO @table VALUES(1453, '2011-10-27', '2011-11-04', 1, 0, NULL);
INSERT INTO @table VALUES(1453, '2011-10-27', '2011-11-04', 1, 1, '7');

CHDCount для количества CHD узлов под Room узлом. Кроме того, сколько Room у меня узлов, столько строк таблицы у меня здесь.

Что касается того, как это должно выглядеть, см. Рисунок ниже :

введите описание изображения здесь

На самом деле, этот код предназначен для поискового запроса бронирования отелей. Итак, мне нужно поработать с этими значениями, которые я получил из параметра XML, чтобы запросить мои таблицы и вернуть доступные номера. Я рассказываю об этом, потому что, может быть, это поможет вам, ребята, довести дело до конца. Я не ищу полный код для системы бронирования номеров. Это было бы так эгоистично.

1. @marc_s спасибо за комментарий к заголовку. Я не уверен, что это хорошее название здесь. Если вы можете предоставить лучшее название, не стесняйтесь изменить его, пожалуйста.

2. Как вы хотите, чтобы выглядела ваша целевая таблица параметров? Вас интересует информация о дочерних узлах или просто количество ADT в комнате? является ли NumberofADT количеством дочерних строк?

3. Не могли бы вы дать более четкое описание требуемых выходных данных — какие значения вам нужны и как их использовать?

4. @StuartAinsworth спасибо за ответ. смотрите обновленный вопрос.

5. @EdHarper спасибо за ответ. смотрите обновленный вопрос.

Ответ №1:

 select S.X.value('ResortID[1]', 'int') as ResortID,
       S.X.value('CheckInDate[1]', 'date') as CheckInDate,
       S.X.value('CheckOutDate[1]', 'date') as CheckOutDate,
       R.X.value('NumberOfADT[1]', 'int') as NumberOfADT,
       R.X.value('count(CHD)', 'int') as CHDCount,
       stuff((select ';' C.X.value('.', 'varchar(3)')
              from R.X.nodes('CHD/Age') as C(X)
              for xml path('')), 1, 1, '') as CHDAges
from @XMLValue.nodes('/SearchQuery') as S(X)
  cross apply S.X.nodes('Room') as R(X)

Ответ №2:

Это должно приблизить вас:

 SELECT  ResortID = @xmlvalue.value('(//ResortID)[1]', 'int')
      , CheckInDate = @xmlvalue.value('(//CheckInDate)[1]', 'date')
      , CheckOutDate = @xmlvalue.value('(//CheckOutDate)[1]', 'date')
      , NumberOfAdt = Room.value('(NumberOfADT)[1]', 'INT')
      , CHDCount = Room.value('count(./CHD)', 'int')
      , CHDAges = Room.query('for $c in ./CHD
                        return concat(($c/Age)[1], ";")').value('(.)[1]',
                                                              'varchar(100)')
FROM    @XMLValue.nodes('/SearchQuery/Room') AS SearchQuery ( Room ) ;