CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Arun Kumar
Arun Kumar

Reputation: 437

Query XML data from a table with XML data type

I have a table as id,createdate, outputxml as columns in which outputxml is an XML Datatype column which has the info as below

<storage xmlns="http://google.com " created-on="2012-12-29">
    <country>India</country>
    <state>tn</point>
    <period type="day" from="2012-12-27" to="2012-12-28" />
    <capacity-total mwh="12898463" mcm="1229.02" country-percentage="6.1%" />
    <net mwh="28004.00" mcm="2.66" />
</storage>

I need the output as:

id, createdate, storage, created-on, country state, period type, from, to, capacity-total mwh, capacity-total mwh, country-percentage

1 20-01-2012 http://google.com 2012-12-29 abc tn day 2012-12-27 2012-12-28 12898463 1229.02 6.1%

I tried using

 select X.N.value(N'(storage/@country)[1]', 'nvarchar(max)'),
        X.N.value(N'(storage/@sso)[2]', 'nvarchar(max)')
   from icissso..ssodataoutput as T
  cross apply T.outputxml.nodes(N'/storage/xmlns') as X(N)

But its not returning any value. So kindly suggest a solution.

Upvotes: 2

Views: 97

Answers (1)

GarethD
GarethD

Reputation: 69829

You need to define your XML Namespace and use that when accessing the values:

DECLARE @T TABLE (X XML);
INSERT @T VALUES ('<storage xmlns="http://google.com" created-on="2012-12-29">
                    <country>India</country>
                    <state>tn</state>
                    <period type="day" from="2012-12-27" to="2012-12-28" />
                    <capacity-total mwh="12898463" mcm="1229.02" country-percentage="6.1%" />
                    <net mwh="28004.00" mcm="2.66" />
                </storage>');

WITH XMLNAMESPACES ('http://google.com' AS x)
SELECT  [CreatedDate] = X.value('/x:storage[1]/@created-on', 'NVARCHAR(MAX)'),
        [Country] = X.value('/x:storage[1]/x:country[1]', 'NVARCHAR(MAX)'),
        [State] = X.value('/x:storage[1]/x:state[1]', 'NVARCHAR(MAX)'),
        [PeriodType] = X.value('/x:storage[1]/x:period[1]/@type', 'NVARCHAR(MAX)'),
        [From] = X.value('/x:storage[1]/x:period[1]/@from', 'NVARCHAR(MAX)'),
        [To] = X.value('/x:storage[1]/x:period[1]/@to', 'NVARCHAR(MAX)'),
        [capacity-total mwh] = X.value('/x:storage[1]/x:capacity-total[1]/@mwh', 'NVARCHAR(MAX)'),
        [capacity-total mcm] = X.value('/x:storage[1]/x:capacity-total[1]/@mcm', 'NVARCHAR(MAX)'),
        [country-percentage] = X.value('/x:storage[1]/x:capacity-total[1]/@country-percentage', 'NVARCHAR(MAX)')
FROM    @T;

SQL Fiddle

Upvotes: 2

Related Questions