Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get data as a datatable from XML column

Posted on 2011-09-13
14
Medium Priority
?
432 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:ulf-jzl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36528571
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
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36528574
I thought you are using programming language.
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36528579
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:ulf-jzl
ID: 36528589
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
 
LVL 9

Expert Comment

by:s_chilkury
ID: 36528594
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
 

Author Comment

by:ulf-jzl
ID: 36528597
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
 

Author Comment

by:ulf-jzl
ID: 36528610
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
 

Author Comment

by:ulf-jzl
ID: 36528621
The RMS values are in the third table.

[Devices] -> [Devices.Tests] -> [Devices.Tests.RMS]
0
 

Author Comment

by:ulf-jzl
ID: 36528632
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
 
LVL 4

Accepted Solution

by:
nost2 earned 2000 total points
ID: 36528782
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
 

Author Comment

by:ulf-jzl
ID: 36528848
nost2: that works perfekt!!
0
 

Author Comment

by:ulf-jzl
ID: 36528868
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
 
LVL 4

Expert Comment

by:nost2
ID: 36529016
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
 

Author Closing Comment

by:ulf-jzl
ID: 36529029
Thx man.. One word, Perfect!!! :)
0

Featured Post

Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question