Extract data from XML string in SQL using XQuery

Question

I am trying to extract data from a XML string which is stored in my table in XMLString column as below..

VId   Uid   Name    TWd     XMLString 
26    jti   jbreti  testell string in xml format
26    Man   Lomond  Mcan    string in xml format
26    mw    mlwTest tewWell string in xml format
26    tot   teapot  te2Well string in xml format

XML string having below format. Its having multi level nodes also..

<well uid="b4e952f9">
    <name>Demo</name>
    <field>Fi Tk</field>
    <country>India</country>
    <county>South India</county>
    <region>SiD</region>
    <block>09-365</block>
    <timeZone>+09:00</timeZone>
    <operator>AACE Oil CO</operator>
    <operatorDiv>AAACE South Australia</operatorDiv>
    <statusWell>unknown</statusWell>
    <wellDatum defaultElevation="true" uid="SL">
        <name>Mean Sea Level</name>
        <code>SL</code>
    </wellDatum>
    <waterDepth uom="ft">269.0256</waterDepth>
    <wellLocation uid="loc-1">
        <latitude uom="dega">-28.601403</latitude>
        <longitude uom="dega">137.444458</longitude>
    </wellLocation>
    <commonData>
        <dTimCreation>2012-04-10T13:17:45.959Z</dTimCreation>
        <dTimLastChange>2013-11-08T14:42:56.340Z</dTimLastChange>
    </commonData>
</well>

I required few nodes from above XML & this few nodes details is also in XML string as below..

<well>
    <name></name>       
    <country></country>
    <block></block>
    <timeZone></timeZone>
    <wellDatum>
        <name></name>
        <code></code>
    </wellDatum>
    <waterDepth></waterDepth>
</well>

Now i need to extract nodes from 1st XML with node values string that are present in 2nd XML string. And out put should be also a XML string. Output string should be as below....

<well uid="b4e952f9">
    <name>Demo</name>
    <country>India</country>
    <block>09-365</block>
    <timeZone>+09:00</timeZone>
    <wellDatum defaultElevation="true" uid="SL">
        <name>Mean Sea Level</name>
        <code>SL</code>
    </wellDatum>
    <waterDepth uom="ft">269.0256</waterDepth>
</well>

This all i want to be done in MSSQL only. Can anyone help me..

What have you tried so far? How did it fail? Have you read e.g. msdn.microsoft.com/en-us/library/ms189075.aspx ? — mb21
– mb21, Commented Oct 3, 2014 at 13:20

Surendra · Accepted Answer · 2014-10-07 13:08:54Z

1

SELECT XMLString.query(
'<well>
    <name>{/well/name/node()}</name>       
    <country>{/well/country/node()}</country>
    <block>{/well/block/node()}</block>
    <timeZone>{/well/timeZone/node()}</timeZone>
    <wellDatum>
        <name>{/well/wellDatum/name/node()}</name>
        <code>{/well/wellDatum/code/node()}</code>
    </wellDatum>
    <waterDepth>{/well/waterDepth/node()}</waterDepth>
 </well>'
),
   XMLString FROM   Well

answered Oct 7, 2014 at 13:08

Surendra

2373 gold badges6 silver badges20 bronze badges

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

1 Comment

Surendra Over a year ago

How to generate that string I'm passing in XMLString.query function?

Collectives™ on Stack Overflow

Extract data from XML string in SQL using XQuery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related