Trouble Getting XML Data into relational format in SQL Server 2014

Question

I'm getting some XML data that I need to 'shred' (I think that's the right term). That is I need to put it into a SQL table. Here's an example, and a query that works, but I was told it was inefficient. Please let me know if you know a more efficient way to do this.

Here's some example XML, 2 queries that don't work, and one that does:

    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

score 3 · Answer 1 · answered Feb 23 '18 at 15:55

The "ineffecency" here is using the Parent Axis to traverse back up the document to get the name. The preferred way to do this is to use multiple APPLY operators. The first one would project out the Customer nodes, and then a second one to project the FavColors nodes. Like this:

SELECT
    CustomerNode.e.value('(Name)[1]', 'nvarchar(max)')      AS CustomerName
   ,FavColorNode.e.value('(.)[1]', 'nvarchar(max)')       AS FavColor
FROM
    @XmlTable   xt
    CROSS APPLY XmlColumn.nodes('/Customers/Customer') CustomerNode(e)
    CROSS APPLY CustomerNode.e.nodes('FavColors/FavColor') FavColorNode(e);

Although the difference between them is probably not material for most scenarios.

Hi, I just saw, that you've posted quite the same answer as I did. I don't know who was first... I was "pulled here" from [this answer](https://stackoverflow.com/a/48912056/5089204) and had my answer readily waiting. Just placed it and went away. However, I'll vote your's up... — Shnugo, Feb 25 '18 at 19:07

score 1 · Accepted Answer · answered Feb 23 '18 at 15:59

As I've told you at the other question before, the solution is a hierarchical call to .nodes()

SELECT
    cust.value('(Name/text())[1]', 'nvarchar(max)')      AS CustomerName
   ,col.value('text()[1]', 'nvarchar(max)')       AS FavColor
FROM
    @XmlTable   xt
    CROSS APPLY XmlColumn.nodes('/Customers/Customer') A(cust)
    CROSS APPLY cust.nodes('FavColors/FavColor') AS B(col) ;

This will first return all <Customer> and - below them - their related <FavColor>:

Customer    FavColor
Sri Patel   Red
Sri Patel   Blue
Sri Patel   Green
Jane Doe    Violet
Jane Doe    Mauve

Trouble Getting XML Data into relational format in SQL Server 2014

2 Answers2

Linked