Returning multiple rows from an XML column query in SQL Server (Revisited)

Question

Answers to the question It was useful to use multiple rows from an XML column query in SQL Server 2008 . But I have an XML dataset with a slightly different structure and you need help getting real query output.

Here is 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)

Two queries return the following exits. The first is beautiful. The second is obviously wrong.

How can I change the SELECT statement to accurately output the data, i.e. to bind the values ​​of MESSAGE_TEXT with the corresponding CATEGORY and STATUS keys?

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

Answer 2

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

:

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

, , "" , "" .