CODEWITHSUNDEEP
sqlxmlsql-server-2008
Purplegoldfish
Purplegoldfish

Reputation: 5284

How to select values from XML in SQL

Im trying to get the values from some XML in SQL but struggling with the final part, how can I get the values from this?

DECLARE @Info XML
SET @Info=N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfDriveData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <DriveData>
    <DriveName>C:\</DriveName>
    <DriveSpace>429223288832</DriveSpace>
  </DriveData>
  <DriveData>
    <DriveName>I:\</DriveName>
    <DriveSpace>228759863296</DriveSpace>
  </DriveData>
</ArrayOfDriveData>'


-- Declare a handle for the xml document    
DECLARE @idoc INT

-- Prepare the xml 
EXEC sp_xml_preparedocument @idoc OUTPUT, @Info

SELECT 
    *
FROM 
    OPENXML (@idoc, '/ArrayOfDriveData/DriveData/,3)

The results I get are:

id | parentid | nodetype | localname| namespaceuri | datatype | prev | text
4   0   1   DriveData   NULL    NULL    NULL    NULL    NULL
5   4   1   DriveName   NULL    NULL    NULL    NULL    NULL
10  5   3   #text   NULL    NULL    NULL    NULL    C:\
6   4   1   DriveSpace  NULL    NULL    NULL    5   NULL
11  6   3   #text   NULL    NULL    NULL    NULL    429223288832
7   0   1   DriveData   NULL    NULL    NULL    4   NULL
8   7   1   DriveName   NULL    NULL    NULL    NULL    NULL
12  8   3   #text   NULL    NULL    NULL    NULL    I:\
9   7   1   DriveSpace  NULL    NULL    NULL    8   NULL
13  9   3   #text   NULL    NULL    NULL    NULL    228759863296

It seems the only columns I need are localname and Text, Ideall I would like the result to look like:

Drive | Space
    C:\  123456

This has probably been asked before but I couldnt see anyone answering how to do it (lots of data on how to get XML from a table, not much on how to get data into a table from XML) and my experience in this area is a bit lacking so i'm stuck on how to finish this off.

Thanks

Upvotes: 2

Views: 897

Answers (2)

Bert
Bert

Reputation: 82459

If you want to stick with openxml its

SELECT 
    DriveName, DriveSpace
FROM 
    OPENXML (@idoc, '/ArrayOfDriveData/DriveData',3)
    with (DriveName varchar(10) 'DriveName', DriveSpace varchar(20) 'DriveSpace)

That does, of course, put the DriveSpace into a varchar, but you can change that to whatever datatype you need.

I do also want to point out that what GSerg posted below is generally considered the more correct way to do it these days. OPENXML is an older method.

Upvotes: 3

GSerg
GSerg

Reputation: 78175

Just plain query it.

select
  t.drivedata.value('DriveName[1]', 'varchar(max)'),
  t.drivedata.value('DriveSpace[1]', 'bigint')
from
  @info.nodes('/ArrayOfDriveData/DriveData') as t(drivedata);

Upvotes: 4

Related Questions