Shredding nested XML data and returning sets

Question

I have xml data with collections nested in multiple levels.

DECLARE @xml AS XML
SET @xml = '<periods xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                <period>
                    <id>1</id>
                    <valid_from>2020-07-31</valid_from>
                    <valid_to xsi:nil="true" />
                    <elements>
                        <element>
                            <from>1</from>
                            <to>2</to>                      
                        </element>
                        <element>
                            <from>1</from>
                            <to>3</to>
                        </element>
                    </elements>
                </period>
                <period>
                    <id>3</id>
                    <valid_from>2020-05-01</valid_from>
                    <valid_to>2020-06-01</valid_to>
                    <elements>
                        <element>
                            <from>7</from>
                            <to>9</to>                      
                        </element>
                        <element>
                            <from>10</from>
                            <to>11</to>
                        </element>
                    </elements>
                </period>
            </periods>'

What I would like is to select all this data into the form:

id  valid_from                  valid_to                    from    to
1   2020-07-31 00:00:00.000     NULL                        1       2
1   2020-07-31 00:00:00.000     NULL                        1       3
3   2020-05-01 00:00:00.000     2020-06-01 00:00:00.000     7       9
3   2020-05-01 00:00:00.000     2020-06-01 00:00:00.000     10      11

The closest I've been able to get is with this query

SELECT 'id' = v.value('id[1]', 'int'),
'dte_from' = v.value('valid_from[1]', 'datetime'),
'dte_to' = v.value('valid_to[1][not(@xsi:nil = "true")]', 'datetime'),
'from' = y.value('from[1][not(@xsi:nil = "true")]', 'int'), 'to' = y.value('to[1]', 'int')
FROM @xml.nodes('/periods/period') x(v)
CROSS APPLY x.v.nodes('/periods/period/elements/element') z(y)

but this just returns

id  dte_from                    dte_to                      from    to
1   2020-07-31 00:00:00.000     NULL                        1       2
1   2020-07-31 00:00:00.000     NULL                        1       3
1   2020-07-31 00:00:00.000     NULL                        7       9
1   2020-07-31 00:00:00.000     NULL                        10      11
3   2020-05-01 00:00:00.000     2020-06-01 00:00:00.000     1       2
3   2020-05-01 00:00:00.000     2020-06-01 00:00:00.000     1       3
3   2020-05-01 00:00:00.000     2020-06-01 00:00:00.000     7       9
3   2020-05-01 00:00:00.000     2020-06-01 00:00:00.000     10      11

Thank you for your time.

FYI *`'string_alias' = expression`* is deprecated and should not be used. Ideally, you should not use string aliases at all, as tend to cause confusion. Stick to non delimit identified names, or use the T-SQL or ANSI identifiers (`[]` and `"` respectively). — Thom A, Aug 03 '20 at 14:12

Thom A · Accepted Answer · 2020-08-03T14:17:41.593

You're, for some reason, redeclaring the root nodes in your CROSS APPLY. You don't need those, and if you remove them you get the results you want:

SELECT pp.p.value('(id/text())[1]', 'int') AS id,
       pp.p.value('(valid_from/text())[1]', 'datetime') AS dte_from,
       pp.p.value('(valid_to[not(@xsi:nil = "true")]/text())[1]', 'datetime') AS dte_to,
       ee.e.value('(from[1][not(@xsi:nil = "true")]/text())[1]', 'int') AS int_from, --don't use FROM, it's a reserve keyword,
       ee.e.value('(to[1]/text())[1]', 'int') AS int_to  --don't use TO, it's a reserve keyword,
FROM @xml.nodes('/periods/period') pp(p)
     CROSS APPLY pp.p.nodes('elements/element') ee(e);

As mentioned in my comment, don't use 'string_alias' = expression, as it's due to be removed from SQL Server, and using string aliases for columns can be quite confusing. I've also given your columns and objects more relevant aliases, or aliases that aren't reserved keywords.

I've also used the text() functionality, when obtaining the values from the XML, as this is faster.

Fantastic, thank you for this. I'm annoyed I didn't think to try this myself. — Matthew Lacey, Aug 03 '20 at 14:17
Made a slight, further amendment, @MatthewLacey, to add `text()`, as it has speed improvements. — Thom A, Aug 03 '20 at 14:20

Shredding nested XML data and returning sets

1 Answers1