parsing XML with SQL

Question

i have this xml:

<workbook>
 <datasources>
    <datasource>
        <connection>
            <relation name='table_1'/>
            <metadata-records class='column'>
                <metadata-record>
                    <remote-name>a1</remote-name>
                    <parent-name>aa1</parent-name>
                </metadata-record>
            </metadata-records>
            <metadata-records class='column'>
                <metadata-record>
                    <remote-name>b1</remote-name>
                    <parent-name>bb1</parent-name>
                </metadata-record>
            </metadata-records>
            <metadata-records class='column'>
                <metadata-record>
                    <remote-name>c1</remote-name>
                    <parent-name>cc1</parent-name>
                </metadata-record>
            </metadata-records>
        </connection>
    </datasource>
    <datasource>
        <connection>
            <relation name='table_2'/>
            <metadata-records class='column'>
                <metadata-record>
                    <remote-name>a2</remote-name>
                    <parent-name>aa2</parent-name>
                </metadata-record>
            </metadata-records>
            <metadata-records class='column'>
                <metadata-record>
                    <remote-name>b2</remote-name>
                    <parent-name>bb2</parent-name>
                </metadata-record>
            </metadata-records>
            <metadata-records class='column'>
                <metadata-record>
                    <remote-name>c2</remote-name>
                    <parent-name>cc2</parent-name>
                </metadata-record>
            </metadata-records>
        </connection>
    </datasource>
  </datasources>
</workbook>

im trying to parse it in SQL so i would get this table:

relation remote-name parent-name
-------- ----------- -----------
table_1  a1          aa1
table_1  b1          bb1
table_1  c1          cc1
table_2  a2          aa2
table_2  b2          bb2
table_2  c2          cc2

i used a few queries and I'm getting too many errors. i think I'm missing something in my basic understanding of XML parsing via SQL.

if you can either help me figure this out or help me find a proper place to learn this that will be great.

thanks.

this is the query I'm executing:

select 
    temp.relation.value('(//relation/@name)[1]','nvarchar(100)')as [relation]
    ,temp.relation.value('(//metadata-records/metadata-record/remote-name)[1]','nvarchar(100)')
    ,temp.relation.value('(//metadata-records/metadata-record/parent-name)[1]','nvarchar(100)')
from
(select 
    c1.query('..') as relation
from 
    tempXml
    cross apply twb.nodes('/workbook/datasources/datasource/connection') as T1(c1)) as temp

GarethD · Accepted Answer · 2017-02-20 17:46:42Z

You need to apply a further nodes function to split each relation into its nodes:

SELECT  relation = t1.c1.value('(relation/@name)[1]', 'NVARCHAR(MAX)'),
        [remote-name] = c2.value('remote-name[1]', 'NVARCHAR(MAX)'),
        [parent-name] = c2.value('parent-name[1]', 'NVARCHAR(MAX)')
FROM    tempXml AS tmp
        CROSS APPLY tmp.twb.nodes('/workbook/datasources/datasource/connection') AS t1 (c1)
        CROSS APPLY t1.c1.nodes('metadata-records/metadata-record') AS t2 (c2);

FULL WORKING EXAMPLE

DECLARE @XML XML = '<workbook>
 <datasources>
    <datasource>
        <connection>
            <relation name="table_1"/>
            <metadata-records class="column">
                <metadata-record>
                    <remote-name>a1</remote-name>
                    <parent-name>aa1</parent-name>
                </metadata-record>
            </metadata-records>
            <metadata-records class="column">
                <metadata-record>
                    <remote-name>b1</remote-name>
                    <parent-name>bb1</parent-name>
                </metadata-record>
            </metadata-records>
            <metadata-records class="column">
                <metadata-record>
                    <remote-name>c1</remote-name>
                    <parent-name>cc1</parent-name>
                </metadata-record>
            </metadata-records>
        </connection>
    </datasource>
    <datasource>
        <connection>
            <relation name="table_2"/>
            <metadata-records class="column">
                <metadata-record>
                    <remote-name>a2</remote-name>
                    <parent-name>aa2</parent-name>
                </metadata-record>
            </metadata-records>
            <metadata-records class="column">
                <metadata-record>
                    <remote-name>b2</remote-name>
                    <parent-name>bb2</parent-name>
                </metadata-record>
            </metadata-records>
            <metadata-records class="column">
                <metadata-record>
                    <remote-name>c2</remote-name>
                    <parent-name>cc2</parent-name>
                </metadata-record>
            </metadata-records>
        </connection>
    </datasource>
  </datasources>
</workbook>';

SELECT  relation = t1.c1.value('(relation/@name)[1]', 'NVARCHAR(MAX)'),
        [remote-name] = c2.value('remote-name[1]', 'NVARCHAR(MAX)'),
        [parent-name] = c2.value('parent-name[1]', 'NVARCHAR(MAX)')
FROM    @XML.nodes('/workbook/datasources/datasource/connection') AS t1 (c1)
        CROSS APPLY t1.c1.nodes('metadata-records/metadata-record') AS t2 (c2);

Collectives™ on Stack Overflow

parsing XML with SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related