How to select values from XML in SQL

Question

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

Searched for #text and got this.... and the answer .... :) Thanks. Saved me much frustration. — Anthony Horne
– Anthony Horne, Commented Jul 28, 2015 at 19:04

GSerg · Accepted Answer · 2011-11-18 15:43:41Z

4

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);

answered Nov 18, 2011 at 15:43

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bert · Accepted Answer · 2011-11-18 16:07:32Z

3

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.

edited Nov 18, 2011 at 16:07

answered Nov 18, 2011 at 15:40

Bert

82.6k17 gold badges205 silver badges166 bronze badges

6 Comments

Sir Crispalot Over a year ago

XPath should be /ArrayOfDriveData/DriveData with no trailing slash I believe and missing a closing quote on 'DriveSpace'

Purplegoldfish Over a year ago

I only get null values back from that :( If there is an easier way than openXML im all for using that, just openXML seemed fairly compact etc

Sir Crispalot Over a year ago

@Purplegoldfish see my comment

Purplegoldfish Over a year ago

Awesome thanks @SirCrispalot when i removed that / it works perfectly, and Bert thanks for such a quick reply I can now get on with the project :D

Bert Over a year ago

Yes, what GSerg posted is easier and a more modern way to do it.

|

Collectives™ on Stack Overflow

How to select values from XML in SQL

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related