sql query to read the nodes of xml

Question

Hi All,


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"/>
</Page>
</Data>
';

From the above xml, i nead the sql query to show the output as below:

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

Kindly help me on the same

Answer 1

See my answer in the original thread.

btw, as you have created this thread - which is good as it is a new question - you should have mentioned this thread as follow up in the post in the original thread.

Answer 2

could you please help me with the query as iam a newbie to this field.

Thanks,

Ram

Answer 3

Start with this and read the links, understanding is important here:

DECLARE @Sample TABLE
    (
      Col1 VARCHAR(255) ,
      Col2 FLOAT
    );

INSERT  INTO @Sample
VALUES  ( 'PDU1A-1 Volt AB', 481.90 ),
        ( 'PDU1A-1 Volt BC', 485.30 );

SELECT  *
FROM    @Sample PIVOT ( MAX(Col2) FOR Col1 IN ( [PDU1A-1 Volt AB], [PDU1A-1 Volt BC] ) ) P;            

Answer 4

thanks a lot stefan, i worked on this and successful finally :)