ulf-jzl
asked on
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.
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
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]
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.
I thought you are using programming language.
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
http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx
http://support.microsoft.com/kb/316005
ASKER
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
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)
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
Check the following link:
http://stackoverflow.com/questions/192398/select-xml-nodes-as-rows
ASKER
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)
ASKER
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.
<RMS><V>1.45</V><V>4.35</V
but my problem is that I need to use JOIN to each it.
ASKER
The RMS values are in the third table.
[Devices] -> [Devices.Tests] -> [Devices.Tests.RMS]
[Devices] -> [Devices.Tests] -> [Devices.Tests.RMS]
ASKER
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?
So a stored proc is needed to do this?
Or is it away to fix it with only one query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
nost2: that works perfekt!!
ASKER
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')
The
CROSS APPLY Havg.nodes('/Havg/V') as t(c)
and
t.c.query('.').value('.','
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 '.'.
ASKER
Thx man.. One word, Perfect!!! :)