CODEWITHSUNDEEP
sqlsql-serverxml
Spaceman
Spaceman

Reputation: 41

SQL - Extract XML from multiple nodes

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.

Upvotes: 0

Views: 1074

Answers (2)

Spaceman
Spaceman

Reputation: 41

Answer as below:

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

Upvotes: 3

ttallierchio
ttallierchio

Reputation: 460

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)

Upvotes: 1

Related Questions