reading the nodes of xml using sql query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

reading the nodes of xml using sql query

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2012-04-26 : 02:13:04

Hi All,

Iam having the xml as shown below :

<?xml version="1.0" encoding="UTF-8"?>
<Data xmlns:ident="http://rddl.xmlinside.net/PowerMeasurement/data/ion/identity/1/"
xmlns:ion="http://rddl.xmlinside.net/PowerMeasurement/data/ion/presentvalue/1/"
xmlns:xlink="http://www.w3.org/1999/xlink"
xmlns="http://rddl.xmlinside.net/PowerMeasurement/data/ion/presentvalue/1/">
<ident:GeneratedBy
ident:dateTime="2012-04-24T08:12:31.385-08:00"
ident:signature="SI-1105B245-02"
ident:type="9510RTU"
ident:namespace="EnterDeviceNamespaceHere"
ident:name="EnterDeviceNameHere"/>
<ident:Configuration ident:tag2=""
ident:tag1="" ident:owner=""
ident:template=""
ident:revision="9510RTUV365">
<ident:Timezone>
<ident:Bias>480</ident:Bias>
<ident:DSTBias>-60</ident:DSTBias>
<ident:DSTStart>2012-03-11T02:00:00.000-08:00</ident:DSTStart>
<ident:DSTEnd>2012-11-04T02:00:00.000-08:00</ident:DSTEnd>
<ident:DSTStart>2013-03-10T02:00:00.000-08:00</ident:DSTStart>
<ident:DSTEnd>2013-11-03T02:00:00.000-08:00</ident:DSTEnd>
</ident:Timezone>
</ident:Configuration>
<Page xlink:title="PDU1A-1"
xlink:role="http://rddl.xmlinside.net/PowerMeasurement/data/ion/presentvalue/dataset/"
xlink:type="simple"
ident:namespace="EnterDeviceNamespaceHere"
ident:name="EnterDeviceNameHere"
xlink:href="http://192.168.0.32/PDU1A-1.xml" ion:module="Web Page 1">
<Item v="481.90" l="PDU1A-1 Volt AB" h="23264"/>
<Item v="485.30" l="PDU1A-1 Volt BC" h="23265"/>
<Item v="482.60" l="PDU1A-1 Volt CA" h="23266"/>
<Item v="205.30" l="PDU1A-1 Volt AN" h="23267"/>
<Item v="205.80" l="PDU1A-1 Volt BN" h="24130"/>
<Item v="208.10" l="PDU1A-1 Volt CN" h="24131"/>
</Page>
</Data>

From the above xml i need a sql query to read the nodes of xml and the output should be as follows :

Title Voltage Load Amps
PDU1A-1 481.90 PDU1A-1 Volt AB 23264
PDU1A-1 485.30 PDU1A-1 Volt BC 23265
PDU1A-1 482.60 PDU1A-1 Volt CA 23266
PDU1A-1 205.30 PDU1A-1 Volt AN 23267
PDU1A-1 205.80 PDU1A-1 Volt BN 24130
PDU1A-1 208.10 PDU1A-1 Volt CN 24131

Kindly help me on the same

rams

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2012-04-26 : 07:01:05

i have got the output guys..thanks for all your support

rams

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources