How to make a select from a table and from relative xml column while splitting into rows the XML column?

Question

I have a table T with two columns. Column A is a varchar column and Column B is a XML column.

Somewhere inside Column B there is always the following parent tag: <Documents> ... </Documents>. Inside there are many <Document>...</Document> children.

I would like to get a result set with two columns:

• Column 1 should contain the same values of Column A;
• Column 2 should contain the content of one <Document>...</Document> only.

E.g. Starting table T:

Column A | Column B
--------------------------------------------------------------------------
abc      | <Documents><Document>Doc 1</Document><Document>Doc 2</Document></Documents>

Expected result:

Column 1 | Column 2
-------------------------------------
abc      |<Document>Doc 1</Document>
abc      |<Document>Doc 2</Document>

I can get Column 2 like this (as seen in the docs):

SELECT T2.C.query('.')
FROM T
CROSS APPLY T.[Column B].nodes('*/Documents/*') as T (C)

but this does not work instead:

SELECT T.[Column A], T2.C.query('.')
FROM T
CROSS APPLY T.[Column B].nodes('*/Documents/*') as T2 (C)

How to get the expected result then?

Answer 1

Here is how to do it.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID CHAR(3), xmldata XML);;
INSERT INTO @tbl (ID, xmldata)
VALUES
('abc', '<Documents><Document>Doc 1</Document><Document>Doc 2</Document></Documents>')
, ('xyz', '<Documents><Document>Doc 10</Document><Document>Doc 20</Document></Documents>');
-- DDL and sample data population, end

SELECT ID
    , c.query('.') AS [Column 2]
FROM @tbl AS tbl
    CROSS APPLY tbl.xmldata.nodes('//Documents/Document') AS t(c);

Output

+-----+-----------------------------+
| ID  |          Column 2           |
+-----+-----------------------------+
| abc | <Document>Doc 1</Document>  |
| abc | <Document>Doc 2</Document>  |
| xyz | <Document>Doc 10</Document> |
| xyz | <Document>Doc 20</Document> |
+-----+-----------------------------+