Get data as a datatable from XML column

Hi!

Need some help to get data from an xml column into a datatable.

This is the data in the xml column
<RMS><V>1.45</V><V>4.35</V><V>3.65</V><</RMS>
I need this to be returned as a datatable

RMS
1.45
4.35
3.65

I got help with this in another post but the problem I now got is how to solve this when you have a lots of JOIN.

This is the code I now have.


SELECT hv.c.[RMS].value('./@V','float') RMS
  FROM [TS.Data].[dbo].[Devices] as d
  JOIN
  [TS.Data].[dbo].[Devices.Tests] as dt
  ON 
  d.[DeviceId] = dt.[DeviceId]
  JOIN
  [TS.Data].[dbo].[Devices.Tests.RMS] hv(c)
  ON dt.[DeviceTestId] = hv.[DeviceTestId]

Open in new window

ulf-jzlAsked:
Who is Participating?
 
nost2Connect With a Mentor Commented:
You can do the select like this:
SELECT t.c.query('.').value('.','float') 
FROM [Devices] as d
  JOIN
  [Devices.Tests] as dt
  ON 
  d.[DeviceId] = dt.[DeviceId]
  JOIN
[Devices.Tests.RMS] hv
on dt.DeviceTestId = hv.DeviceTestId
CROSS APPLY RMS.nodes('/RMS/V') as t(c)

Open in new window

0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
did you try obtaining it as a recordset and assign as databale. Since there is no more detailed information I am not sure whether this helps.
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
I thought you are using programming language.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Shanmuga SundaramDirector of Software EngineeringCommented:
if you want to do without programming then this links should help

http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx
http://support.microsoft.com/kb/316005
0
 
ulf-jzlAuthor Commented:
I just want to run this in MSSMS and it should return the data as a table. but it wont work.

dwkor: got his for me. and it does what I want.. but I can't get this to work on my tables with my joins


declare        @X xml = N'<RMS><R V="1"/>><R V="3"/>><R V="2"/></RMS>'        select        t.c.value('./@V','int')from        @X.nodes('/RMS/R') as t(c)

Open in new window

0
 
s_chilkuryCommented:
You have similar <v> tags due to which its hard to understand by the following query:

Check the following link:

http://stackoverflow.com/questions/192398/select-xml-nodes-as-rows
0
 
ulf-jzlAuthor Commented:
This is easier to read-
declare @X xml = N'<RMS><R V="1"/>><R V="3"/>><R V="2"/></RMS>'        
select
  t.c.value('./@V','int')
from        
  @X.nodes('/RMS/R') as t(c)

Open in new window

0
 
ulf-jzlAuthor Commented:
I like to the same with this
<RMS><V>1.45</V><V>4.35</V><V>3.65</V><</RMS>

but my problem is that I need to use JOIN to each it.
0
 
ulf-jzlAuthor Commented:
The RMS values are in the third table.

[Devices] -> [Devices.Tests] -> [Devices.Tests.RMS]
0
 
ulf-jzlAuthor Commented:
Or must I always get the data first into a variable of type xml and then use dwkor's query?

So a stored proc is needed to do this?

Or is it  away to fix it with only one query?
0
 
ulf-jzlAuthor Commented:
nost2: that works perfekt!!
0
 
ulf-jzlAuthor Commented:
nost2: can you briefly explain how this query works, new to query xml.

The
CROSS APPLY Havg.nodes('/Havg/V') as t(c)
and  
t.c.query('.').value('.','float')
0
 
nost2Commented:
CROSS APPLY lets you use a table-valued function in a query. Nodes is a method for returning nodes of an XML document. Query and value are for filtering nodes and extracting values from the nodes, in this case we are interested in all of the contents of all the nodes so we use '.'.
0
 
ulf-jzlAuthor Commented:
Thx man.. One word, Perfect!!! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.