sql-server - 从查询 SQL Server 中的 XML 列返回多行(重新访问)

Question

问题的答案 Returning multiple rows from querying XML column in SQL Server 2008很有帮助。但是我有一个结构略有不同的 XML 数据集，需要帮助才能获得有效的查询输出。

这是演示我的问题的代码。

DECLARE @XML_In XML = '
<ROOT>
  <PROCESS_RESULT>
    <CATEGORY>ABC</CATEGORY>
    <STATUS>ERROR</STATUS>
    <PROCESS_RESULT_MSG>
      <MESSAGE_TEXT>ABC Process Category Error</MESSAGE_TEXT>
    </PROCESS_RESULT_MSG>
  </PROCESS_RESULT>
  <PROCESS_RESULT>
    <CATEGORY>XYZ</CATEGORY>
    <STATUS>ERROR</STATUS>
    <PROCESS_RESULT_MSG>
      <MESSAGE_TEXT>XYZ Process Category Error</MESSAGE_TEXT>
    </PROCESS_RESULT_MSG>
  </PROCESS_RESULT>
</ROOT>'

DECLARE @XMLTab TABLE ( MyXMLTable XML)
INSERT INTO @XMLTab ( MyXMLTable ) VALUES( @XML_In )
SELECT MyXMLTable FROM @XMLTab

SELECT b.query('data(CATEGORY)')     AS CATEGORY
      ,b.query('data(STATUS)')       AS STATUS
      ,a.query('data(MESSAGE_TEXT)') AS MESSAGE_TEXT
FROM  @XMLTab
       CROSS APPLY 
       MyXMLTable.nodes('ROOT/PROCESS_RESULT/PROCESS_RESULT_MSG') x(a)
       CROSS APPLY
       MyXMLTable.nodes('ROOT/PROCESS_RESULT') y(b)

这两个查询返回以下输出。第一个很好。第二个显然是不正确的。

如何更改 SELECT 语句以准确输出数据，即将 MESSAGE_TEXT 值与正确的 CATEGORY 和 STATUS 键相关联？

Answer 1

您不需要添加另一个CROSS APPLY 来获得与XML 结构不同的级别。只需在 .value() 函数中指定完整路径，相对于在 .nodes() 函数中指定的路径:

DECLARE @XML_In XML = '
<ROOT>
  <PROCESS_RESULT>
    <CATEGORY>ABC</CATEGORY>
    <STATUS>ERROR</STATUS>
    <PROCESS_RESULT_MSG>
      <MESSAGE_TEXT>ABC Process Category Error</MESSAGE_TEXT>
    </PROCESS_RESULT_MSG>
  </PROCESS_RESULT>
  <PROCESS_RESULT>
    <CATEGORY>XYZ</CATEGORY>
    <STATUS>ERROR</STATUS>
    <PROCESS_RESULT_MSG>
      <MESSAGE_TEXT>XYZ Process Category Error</MESSAGE_TEXT>
    </PROCESS_RESULT_MSG>
  </PROCESS_RESULT>
</ROOT>'

DECLARE @XMLTab TABLE ( MyXMLTable XML)
INSERT INTO @XMLTab ( MyXMLTable ) VALUES( @XML_In )

SELECT tab.col.query('data(CATEGORY)')     AS [CATEGORY],
       tab.col.query('data(STATUS)')       AS [STATUS],
       tab.col.query('data(PROCESS_RESULT_MSG/MESSAGE_TEXT)') AS [MESSAGE_TEXT]
FROM  @XMLTab
       CROSS APPLY
       MyXMLTable.nodes('ROOT/PROCESS_RESULT') tab(col);

返回:

CATEGORY    STATUS        MESSAGE_TEXT
---------   -----------   ------------
ABC         ERROR         ABC Process Category Error
XYZ         ERROR         XYZ Process Category Error

另外，通过AS命名结果集字段时，最好将其括在方括号中(如上面的示例代码所示)。