sql-server - SQL Server 从 XML 参数到表 - 使用可选的子节点

Question

在 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 值，下面的 Insert 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为Room节点下CHD节点的个数。另外，我有多少个 Room 节点，我在这里有多少个表行。

至于它应该是什么样子，请看下图:

Actually, this code is for hotel reservation search query. So, I need to work with these values I got from XML parameter to query my tables and return available rooms. I am telling this because maybe it helps you guys to see it through. I am not looking for a complete code for room reservation system. That would be so selfish.

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