CODEWITHSUNDEEP
sql-serverxmlxquery
Surendra
Surendra

Reputation: 237

Extract data from XML string in SQL using XQuery

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..

Upvotes: 0

Views: 1099

Answers (1)

Surendra
Surendra

Reputation: 237

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

Upvotes: 1

Related Questions