从 SQL Server 中查询 XML 列返回多行(重新访问) [英] Returning multiple rows from querying XML column in SQL Server (Revisited)

问题描述

问题的答案

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

解决方案

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

DECLARE @XML_In XML = '<根目录><PROCESS_RESULT><CATEGORY>ABC</CATEGORY><STATUS>错误</STATUS><PROCESS_RESULT_MSG><MESSAGE_TEXT>ABC处理类别错误</MESSAGE_TEXT></PROCESS_RESULT_MSG></PROCESS_RESULT><PROCESS_RESULT><类别>XYZ</类别><STATUS>错误</STATUS><PROCESS_RESULT_MSG><MESSAGE_TEXT>XYZ进程类别错误</MESSAGE_TEXT></PROCESS_RESULT_MSG></PROCESS_RESULT>'声明@XMLTab 表(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]从@XMLTab交叉申请MyXMLTable.nodes('ROOT/PROCESS_RESULT') tab(col);

返回:

CATEGORY STATUS MESSAGE_TEXT--------- ----------- ------------ABC 错误 ABC 过程类别错误XYZ 错误 XYZ 过程类别错误

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

The Answers to the Question Returning multiple rows from querying XML column in SQL Server 2008 were helpful. But I have an XML data set with a slightly different structure and need help getting valid query output.

Here's the code that demonstrates my problem.

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)

The two queries return the following outputs. The first is fine. The second is obviously incorrect.

How might I change the SELECT statement to accurately output the data, i.e., relating the MESSAGE_TEXT values to the proper CATEGORY and STATUS key?

解决方案

You don't need to add another CROSS APPLY just to get a different level from the XML structure. Just specify the full path in the .value() function, relative to the path specified in the .nodes() function:

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

Returns:

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

Also, when naming a result set field via AS, it is best to enclose it in square-brackets (as shown in the example code above).

这篇关于从 SQL Server 中查询 XML 列返回多行(重新访问)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！