Read in SQL Server XML generated with FOR XML AUTO

Question

I am currently working with reading data in SQL Server that was previously exported using FOR XML AUTO:

SELECT  object_id, name,
        (   SELECT  schema_id, name
            FROM    sys.schemas [schema]
            WHERE   [schema].schema_id = [table].schema_id
            FOR XML AUTO, TYPE
        ),
        (   SELECT  index_id, name, type, is_unique
            FROM    sys.indexes [index] 
            WHERE   [index].object_id = [table].object_id
            FOR XML AUTO, TYPE
        )
FROM    sys.tables [table]
WHERE   object_id = 357576312
FOR XML AUTO

I get this XML:

<table object_id="357576312" name="Address">
  <schema schema_id="5" name="SalesLT" />
  <index index_id="1" name="PK_Address_AddressID" type="1" is_unique="1" />
  <index index_id="2" name="AK_Address_rowguid" type="2" is_unique="1" />
  <index index_id="3" name="IX_Address_AddressLine1_AddressLine2_City_StateProvince_PostalCode_CountryRegion" type="2" is_unique="0" />
  <index index_id="4" name="IX_Address_StateProvince" type="2" is_unique="0" />
</table>

I need to get the same output as this:

SELECT  [table].object_id table_object_id, [table].name table_name, [schema].schema_id, [schema].name schema_name, [index].index_id, [index].name object_name, [index].type, [index].is_unique
FROM    sys.tables [table]
JOIN    sys.schemas [schema] ON [schema].schema_id = [table].schema_id
JOIN    sys.indexes [index] ON [index].object_id = [table].object_id

I started with a draft I couldn't get anywhere yet.

SELECT  T.Data.value('@object_id', ' INT') table_object_id,
        T.Data.value('@name', ' NVARCHAR(128)') table_name,
        T.Data.value('@object_id', ' INT') schema_object_id,
        T.Data.value('@name', ' NVARCHAR(128)') schema_name,
        T.Data.value('@index_id', ' INT') index_id,
        T.Data.value('@name', ' NVARCHAR(128)') index_name,
        T.Data.value('@type', ' TINYINT') type,
        T.Data.value('@is_unique', ' BIT') is_unique
FROM    @xml.nodes('table') T(Data)
;

How do I make it work for the child? Or is it there any easier way to read the XML to output as a flat table?

Answer 1

Here is how to do it.

The CROSS APPLY construct is simulating relationships.

SQL

DECLARE @xml XML = 
N'<table object_id="357576312" name="Address">
    <schema schema_id="5" name="SalesLT"/>
    <index index_id="1" name="PK_Address_AddressID" type="1" is_unique="1"/>
    <index index_id="2" name="AK_Address_rowguid" type="2" is_unique="1"/>
    <index index_id="3"
           name="IX_Address_AddressLine1_AddressLine2_City_StateProvince_PostalCode_CountryRegion"
           type="2" is_unique="0"/>
    <index index_id="4" name="IX_Address_StateProvince" type="2" is_unique="0"/>
</table>';

SELECT t.value('@object_id', 'INT') AS table_object_id
    , t.value('@name', 'NVARCHAR(128)') AS table_name
    , s.value('@schema_id', 'INT') AS schema_object_id
    , s.value('@name', 'NVARCHAR(128)') AS schema_name
    , i.value('@index_id', 'INT') AS index_id
    , i.value('@name', 'NVARCHAR(128)') AS index_name
    , i.value('@type', 'TINYINT') AS type
    , i.value('@is_unique', 'BIT') AS is_unique
FROM @xml.nodes('table') AS t(t)
    CROSS APPLY t.t.nodes('schema') AS s(s)
    CROSS APPLY t.t.nodes('index') AS i(i);

Output

+-----------------+------------+------------------+-------------+----------+----------------------------------------------------------------------------------+------+-----------+
| table_object_id | table_name | schema_object_id | schema_name | index_id |                                    index_name                                    | type | is_unique |
+-----------------+------------+------------------+-------------+----------+----------------------------------------------------------------------------------+------+-----------+
|       357576312 | Address    |                5 | SalesLT     |        1 | PK_Address_AddressID                                                             |    1 |         1 |
|       357576312 | Address    |                5 | SalesLT     |        2 | AK_Address_rowguid                                                               |    2 |         1 |
|       357576312 | Address    |                5 | SalesLT     |        3 | IX_Address_AddressLine1_AddressLine2_City_StateProvince_PostalCode_CountryRegion |    2 |         0 |
|       357576312 | Address    |                5 | SalesLT     |        4 | IX_Address_StateProvince                                                         |    2 |         0 |
+-----------------+------------+------------------+-------------+----------+----------------------------------------------------------------------------------+------+-----------+