从SQL Server中查询XML列返回多行（重访）

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.

问题的答案从SQL Server 2008中查询XML列返回多行是有帮助的。但我有一个XML数据集，结构略有不同，需要帮助获得有效的查询输出。

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?

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

2 个解决方案

#1

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:

您不需要添加另一个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);

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

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

#2

After a day of spinning my wheels on this problem, but in SQL 2014, a co-worker showed me this:

经过一天的轮换我的问题，但在SQL 2014中，一位同事向我展示了这一点：

    DECLARE @XmlReportParameters NVARCHAR (MAX) = N'<?xml version="1.0" encoding="utf-16"?>
<Customers>
    <Customer>
        <Name>Sri Patel</Name>
        <FavColors>
            <FavColor>Red</FavColor>
            <FavColor>Blue</FavColor>
            <FavColor>Green</FavColor>
        </FavColors>
    </Customer>
    <Customer>
        <Name>Jane Doe</Name>
        <FavColors>
            <FavColor>Violet</FavColor>
            <FavColor>Mauve</FavColor>
        </FavColors>
    </Customer>
</Customers>
'

DECLARE @doc XML;
DECLARE @XmlTable TABLE
(
    XmlColumn XML NULL
);

SET @doc = @XmlReportParameters;

INSERT INTO @XmlTable
    ( XmlColumn )
VALUES
    ( @doc )

-- Wrong Way
SELECT
    tbl.col.value('(Name)[1]', 'nvarchar(max)')      AS CustomerName
   ,tbl.col.value('(FavColors/FavColor)[1]', 'nvarchar(max)')       AS FavColor
FROM
    @XmlTable   xt
    CROSS APPLY XmlColumn.nodes('/Customers/Customer') tbl(col);

-- Still wrong (but I'm not sure why)
SELECT
    tbl.col.value('(../Name)[1]', 'nvarchar(max)')      AS CustomerName
   ,tbl.col.value('(FavColor)[1]', 'nvarchar(max)')       AS FavColor
FROM
    @XmlTable   xt
    CROSS APPLY XmlColumn.nodes('/Customers/Customer/FavColors') tbl(col);

-- Right Way
SELECT
    tbl.col.value('(../../Name)[1]', 'nvarchar(max)')      AS CustomerName
   ,tbl.col.value('(.)[1]', 'nvarchar(max)')       AS FavColor
FROM
    @XmlTable   xt
    CROSS APPLY XmlColumn.nodes('/Customers/Customer/FavColors/FavColor') tbl(col);

Returns:

返回：

CustomerName FavColor
------------ ----------
Sri Patel    Red
Jane Doe     Violet

CustomerName FavColor
------------ ----------
Sri Patel    Red
Jane Doe     Violet

CustomerName FavColor
------------ ----------
Sri Patel    Red
Sri Patel    Blue
Sri Patel    Green
Jane Doe     Violet
Jane Doe     Mauve

I didn't find anywhere else that told me I have to work "up" from the nodes path, I cannot work my way "down" to sub parts.

我没有发现其他任何告诉我我必须从节点路径“向上”工作的东西，我不能按照“向下”的方式工作到子部分。