SQL - Extract XML from multiple nodes

Question

I've done a load of research and cannot seem to string together the SQL to extract the required data from an XML field.

<vItem>
<jobScript>
    <node guid="7606bd90-98df-4572-accd-5b41ec5605dc">
        <subNodes>
            <node guid="17f8e275-d4f6-47c0-a5e4-80da658f4097">
                <execute taskVersionGuid="5fc17d5c-7264-461f-ae38-753d703f3c99" />
            </node>
            <node guid="5fe2233c-9e3a-44be-aa20-aea2c8dcbd4a">
                <execute taskVersionGuid="f55dc069-46ff-427e-920f-5f1c3fc3ad09" />
            </node>
            <node guid="ecd6a7b5-a3be-483c-acf8-64ba1c289088">
                <execute taskVersionGuid="5220d97c-6e8f-400a-b814-aa7d84942c20" />
            </node>
        </subNodes>
    </node>
</jobScript>

I'm trying to extract the taskVersionGuid from each node. In the scenario, there could be anywhere between 1 and 10 taskVersionGuids, however the example I have above has 3.

Any help with this would be appreciated.

Thanks

Edit

I have tried the below also:

declare @XML xml

set @XML = 
'

<vItem>
    <jobScript>
        <node guid="7606bd90-98df-4572-accd-5b41ec5605dc">
            <subNodes>
                <node guid="17f8e275-d4f6-47c0-a5e4-80da658f4097">
                    <execute taskVersionGuid="5fc17d5c-7264-461f-ae38-        
753d703f3c99" />
                </node>
                <node guid="5fe2233c-9e3a-44be-aa20-aea2c8dcbd4a">
                    <execute taskVersionGuid="f55dc069-46ff-427e-920f-    
5f1c3fc3ad09" />
                </node>
                <node guid="ecd6a7b5-a3be-483c-acf8-64ba1c289088">
                    <execute taskVersionGuid="5220d97c-6e8f-400a-b814-
aa7d84942c20" />
                </node>
            </subNodes>
        </node>
    </jobScript>
</vItem>
'

select T.N.query('.')
from @XML.nodes('/vItem/jobScript/node/subNodes/node/execute') as T(N)

However, this results in the following:

<execute taskVersionGuid="5fc17d5c-7264-461f-ae38-753d703f3c99" />
<execute taskVersionGuid="f55dc069-46ff-427e-920f-5f1c3fc3ad09" />
<execute taskVersionGuid="5220d97c-6e8f-400a-b814-aa7d84942c20" />

Whereas I'm trying to receive the value of taskVersionGuid.

Thanks again.

I have tried applying the below article, but can't seem to make it work. stackoverflow.com/questions/8718611/… — Spaceman
– Spaceman, Commented Sep 11, 2017 at 16:24
XML support is highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Sep 11, 2017 at 18:24

Spaceman · Accepted Answer · 2017-09-11 16:42:51Z

3

Answer as below:

select T.N.value('@taskVersionGuid[1]', 'uniqueidentifier')
from @XML.nodes('/vItem/jobScript/node/subNodes/node/execute') as T(N)

answered Sep 11, 2017 at 16:42

Spaceman

416 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ttallierchio · Accepted Answer · 2017-09-11 16:33:40Z

1

What you need to do is turn your xml into a table so you can query it. below is an example of the query you will need to grab the values from the nodes.

DECLARE @xml AS XML = '<jobScript>
<node guid="7606bd90-98df-4572-accd-5b41ec5605dc">
    <subNodes>
        <node guid="17f8e275-d4f6-47c0-a5e4-80da658f4097">
            <execute taskVersionGuid="5fc17d5c-7264-461f-ae38-753d703f3c99" />
        </node>
        <node guid="5fe2233c-9e3a-44be-aa20-aea2c8dcbd4a">
            <execute taskVersionGuid="f55dc069-46ff-427e-920f-5f1c3fc3ad09" />
        </node>
        <node guid="ecd6a7b5-a3be-483c-acf8-64ba1c289088">
            <execute taskVersionGuid="5220d97c-6e8f-400a-b814-aa7d84942c20" />
        </node>
    </subNodes>
</node>
</jobScript>'

SELECT a.value('.', 'varchar(max)')
FROM @xml.nodes('/jobScript/node/subNodes/node/execute/@taskVersionGuid') a(a)

answered Sep 11, 2017 at 16:33

ttallierchio

4607 silver badges17 bronze badges

1 Comment

Spaceman Over a year ago

Hi @ttallierchio, Appreciate the response, I found a solution and answered my question before seeing your post. Appreciate your help. Thanks

Collectives™ on Stack Overflow

SQL - Extract XML from multiple nodes

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related