CODEWITHSUNDEEP
sqlsql-serverxml-parsing
Khan Aamir
Khan Aamir

Reputation: 145

sql to extraction xml form column

This is the sample of xml in my table column;

Table name t005, column name ACTIVITYDETAIL, data type xml.

Sample of xml

<root> 
   <Parameter> 
      <Param>SearcgBy</Param> 
      <Value>ALL</Value> 
   </Parameter>         
   <Parameter> 
      <Param>SearchText</Param> 
      <Value>SA</Value> 
   </Parameter>    
</root>

Now I want output as 

Param                value
SearchBy               ALL
SearchText             SA

I tried so many ways and here is my last try.

SELECT 
    p.value('(./Parameter/node())[1]', 'VARCHAR(8000)') as firstName,
    p.value('(./Parameter/node())[2]', 'VARCHAR(8000)') as lastName
FROM 
    t005 
CROSS APPLY 
    ACTIVITYDETAIL.nodes('/root') t(p)

Please help me.

Upvotes: 1

Views: 65

Answers (1)

podiluska
podiluska

Reputation: 51494

Try this

 SELECT 
      t.p.value('Param[1]','varchar(20)') as Param,
      t.p.value('Value[1]','varchar(20)') as Value
 FROM T005 CROSS APPLY ACTIVITYDETAIL.nodes('/root/Parameter') t(p)

Upvotes: 3

Related Questions