reading xml nodes from xml file using sql query

Question

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

Answer 1

E.g.

DECLARE @Data XML = '
<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>
';

WITH XMLNAMESPACES  (DEFAULT 'http://rddl.xmlinside.net/PowerMeasurement/data/ion/presentvalue/1/' )
SELECT  Item.value('@l', 'NVARCHAR(255)') AS l ,
		Item.value('@v', 'NVARCHAR(255)') AS v ,
		Item.value('@h', 'NVARCHAR(255)') AS h
FROM    @Data.nodes('/Data/Page/Item') DataPage ( Item );

Answer 2

Hi Stefan,

Thanks a lot for the quick reply, you really saved me. I am having a small doubt here.... i also want to show one more column with Titlle that reads from <page> tag where it is in the above query ( i.e , <Page xlink:title="PDU1A-1" ) which means the final ouput will be as:

Title                 L                            v                     h
PDU1A-1        PDU1A-1 Volt AB       481.90 23264
PDU1A-1          PDU1A-1 Volt BC       485.30 23265

Thanks,

Ram

Answer 3

E.g.

WITH XMLNAMESPACES  (DEFAULT 'http://rddl.xmlinside.net/PowerMeasurement/data/ion/presentvalue/1/' , 'http://www.w3.org/1999/xlink' AS xlink)
SELECT  Item.value('@l', 'NVARCHAR(255)') AS l ,
		Item.value('@v', 'NVARCHAR(255)') AS v ,
		Item.value('@h', 'NVARCHAR(255)') AS h ,
		Item.value('../@xlink:title', 'NVARCHAR(255)') AS title
FROM    @Data.nodes('/Data/Page/Item') DataPage ( Item );

Answer 4

Really thanks a lot stefan, you made my day happy :) Thanks a lot.

Answer 5

You should avoid the Parent Axis operator (..) for performance reasons as described here:

Solving Parent Axis Access Performance Issues In XML Access In SQL Server 2005
http://blogs.technet.com/b/wardpond/archive/2006/02/19/database-programming-applying-apply-solving-parent-axis-access-performance-issues-in-xml-access-in-sql-server-2005.aspx

Do this instead:

DECLARE @xml XML

SET @xml = '<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>'
 

;WITH XMLNAMESPACES( DEFAULT 'http://rddl.xmlinside.net/PowerMeasurement/data/ion/presentvalue/1/', 'http://www.w3.org/1999/xlink' AS xlink )
SELECT 

	p.c.value('@xlink:title', 'VARCHAR(100)') AS title,
	i.c.value('@v', 'NUMERIC(10,2)') AS v,
	i.c.value('@l', 'VARCHAR(100)') AS l,
	i.c.value('@h', 'INT') AS h
FROM @xml.nodes('Data/Page') p(c)
	CROSS APPLY p.c.nodes('Item') i(c)

Answer 6

thanks a lot bob

Answer 7

Hi Stefan and bob i have one more question for you, this is the new change requested by the client, from the same above xml i need the output to be displayed as follows: 

PDU1A-1 Volt AB  PDU1A-1 Volt BC
481.90           485.30

kindly help me on the same

Answer 8

Basically you need the PIVOT operator. But in your case, as you don't know the columns you need a dynamic pivot.

Answer 9

the columns i need are that are that the 'l' value in the item tag should be displayed as column header and the 'v' value should be displayed under the 'l' column like :

PDU1A-1 Volt AB  PDU1A-1 Volt BC
481.90           485.30

kindly help me with the query. its very urgent from the client end stefan. iam really thankful to you