SQL- Querying XML attributes from XML Column

Read XML column attributes in to two columns DimType and Dimvalue  from the below example also retrieve the data matching values[DimTypes (WIDTH or Height)].

‘<dimensions>

<dimension name=”width”  value=”12.77″/>

<dimension name=”height”  value=”0.14″/>

<dimension name=”depth”      value=”12.92″/>

</dimenstions>’

Download SQL :-  https://gallery.technet.microsoft.com/SQL-Querying-XML-f700bbb0

 SQL:-

DECLARE @xml XML
SELECT @xml = 
'<dimensions>
  <dimension name="height" value="0.14" /> 
  <dimension name="width"  value="12.77"/> 
</dimensions>'

SELECT  x.v.value('@name[1]', 'VARCHAR(100)') AS dimtype ,
        x.v.value('@value[1]', 'VARCHAR(100)') AS dimvalue
FROM    @xml.nodes('/dimensions/dimension') x(v)

second1

create table #demo (field1 xml)
insert into #demo (field1) 
values ('<dimensions>
  <dimension name="height" value="0.14" /> 
  <dimension name="width"  value="12.77"/> 
</dimensions>')

SELECT  x.v.value('@name[1]', 'VARCHAR(100)') AS dimtype ,
        x.v.value('@value[1]', 'VARCHAR(100)') AS dimvalue
from #demo
cross apply field1.nodes('/dimensions/dimension') x(v)

first1

Select data for DimType “height and Width”

DECLARE @xml XML
SELECT @xml = 
'<dimensions>
  <dimension name="height" value="0.14" /> 
  <dimension name="width"  value="12.77"/> 
   <dimension name="depth"   value="12.92"/>
</dimensions>'

SELECT  x.v.value('@name[1]', 'VARCHAR(100)') AS dimtype ,
        x.v.value('@value[1]', 'VARCHAR(100)') AS dimvalue
FROM    @xml.nodes('/dimensions/dimension[@name = "height" or @name = "width"]') x(v)

third1

Using Temp table and Join –

create table #demo (field1 xml)
insert into #demo (field1) 
values ('<dimensions>
 <dimension name="height" value="0.14" /> 
 <dimension name="width" value="12.77"/> 
</dimensions>')

SELECT x.v.value('@name[1]', 'VARCHAR(100)') AS dimtype ,
 x.v.value('@value[1]', 'VARCHAR(100)') AS dimvalue
from #demo
cross apply field1.nodes('/dimensions/dimension[@name = "height" or @name = "width"]') x(v)

fourth1

Using SQL Where Clause to fetch dimension type data ‘HEIGHT’ or ‘WIDTH’

CREATE TABLE #demo (field1 xml)
INSERT INTO #demo (field1) 
values ('<dimensions>
  <dimension name="height" value="0.14" /> 
  <dimension name="width"  value="12.77"/> 
  <dimension name="depth"   value="12.92"/>
</dimensions>')

select * from #demo

select 
dimtype,
dimvalue
FROM
(
SELECT  x.v.value('@name[1]', 'VARCHAR(100)') AS dimtype ,
        x.v.value('@value[1]', 'VARCHAR(100)') AS dimvalue
from #demo
cross apply field1.nodes('/dimensions/dimension') x(v)
)T
where
T.dimtype in('height','width')
Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL XML and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s