SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reading XML from column


Reading XML from column

Author
Message
cor_perlee
cor_perlee
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 377
Hello all,

This question has probably been asked before but I just can't figure out what to do.

I have a table:
-------------------------------------------------------------------------------------------------------------------------------------------------
/****** Object: Table [dbo].[Resourcepools_5_XML] Script Date: 06/13/2013 09:59:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Resourcepools_5_XML](
[id] [int] IDENTITY(1,1) NOT NULL,
[config_spec] [xml] NULL
) ON [PRIMARY]

GO[size="1"][/size]


-------------------------------------------------------------------------------------------------------------------------------------------------
In this table there are xml documents that look like this:


-------------------------------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
-<obj xsi:type="ResourceConfigSpec" versionId="5.0" xmlns="urn:vim25" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <entity type="ResourcePool">resgroup-161074</entity> -<cpuAllocation> <reservation>0</reservation> <expandableReservation>true</expandableReservation> <limit>1</limit> -<shares> <shares>16000</shares> <level>custom</level> </shares> </cpuAllocation> -<memoryAllocation> <reservation>741</reservation> <expandableReservation>true</expandableReservation> <limit>-1</limit> -<shares> <shares>522240</shares> <level>custom</level> </shares> </memoryAllocation> </obj>

-------------------------------------------------------------------------------------------------------------------------------------------------

What I need is to get the values for <shares> so in this case : 16000 and 522240

I have been reading posts and articles about this but I just don't understand the syntax for the query that is needed for this.

I have :
-------------------------------------------------------------------------------------------------------------------------------------------------

SELECT config_spec.value('(shares)[1]', 'VARCHAR(200)') as shares
FROM (table) CROSS APPLY config_spec.nodes('/cpuallocation/shares') t(p)

But is returns no values.

The thing is that I don't understand how to define the fields in the query. Also the CROSS APPLY eludes me.

Any help would greatly be appreciated.


Cor
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3624 Visits: 1779
I think the main issue that you may have had is the missing namespace declaration in your query. Your example XML has a namespace in there so you need to specify that as part of your query.. here is an updated example:


;with xmlnamespaces (default 'urn:vim25')
select t.c.value('(shares)[1]', 'varchar(200)')
from (table)
cross apply @xml.nodes('(/obj/cpuAllocation/shares, /obj/memoryAllocation/shares)') t(c)



this query returns the value of the <shares> node under both cpuAllocation and memoryAllocation node..
cor_perlee
cor_perlee
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 377
It worked!

Thanks so much. Really appreciate it Smile

Cheers!
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3624 Visits: 1779
No problem... thanks for the feedback Smile
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13630 Visits: 8013
cor_perlee (6/17/2013)
It worked!

Thanks so much. Really appreciate it Smile

Cheers!


I know I am a little late here but this:


;with xmlnamespaces (default 'urn:vim25')
select t.c.value('(shares)[1]', 'varchar(200)')
from (table)
cross apply @xml.nodes('(/obj/cpuAllocation/shares, /obj/memoryAllocation/shares)') t(c)



is getting all the data returned from the @xml variable. In this query you are not getting any information from the table. In fact, the query below would produce the same result:


DECLARE @xml xml='
<obj xsi:type="ResourceConfigSpec"
versionId="5.0"
xmlns="urn:vim25"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<entity type="ResourcePool">resgroup-161074</entity>
<cpuAllocation>
<reservation>0</reservation>
<expandableReservation>true</expandableReservation>
<limit>1</limit>
<shares>
<shares>26555</shares>
<level>custom</level>
</shares>
</cpuAllocation>
<memoryAllocation>
<reservation>741</reservation>
<expandableReservation>true</expandableReservation>
<limit>-1</limit>
<shares>
<shares>522240</shares>
<level>custom</level>
</shares>
</memoryAllocation>
</obj>';

;with xmlnamespaces (default 'urn:vim25')
select t.c.value('(shares)[1]', 'varchar(200)')
FROM @xml.nodes('(/obj/cpuAllocation/shares, /obj/memoryAllocation/shares)') t(c)



I think you were looking for something more like this:


--(1) Set up the sample data
--------------------------------------------------------------
USE tempdb
GO

DECLARE @xml1 xml='
<obj xsi:type="ResourceConfigSpec"
versionId="5.0"
xmlns="urn:vim25"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<entity type="ResourcePool">resgroup-161074</entity>
<cpuAllocation>
<reservation>0</reservation>
<expandableReservation>true</expandableReservation>
<limit>1</limit>
<shares>
<shares>16000</shares>
<level>custom</level>
</shares>
</cpuAllocation>
<memoryAllocation>
<reservation>741</reservation>
<expandableReservation>true</expandableReservation>
<limit>-1</limit>
<shares>
<shares>522240</shares>
<level>custom</level>
</shares>
</memoryAllocation>
</obj>';
DECLARE @xml2 xml='
<obj xsi:type="ResourceConfigSpec"
versionId="5.0"
xmlns="urn:vim25"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<entity type="ResourcePool">resgroup-161074</entity>
<cpuAllocation>
<reservation>0</reservation>
<expandableReservation>true</expandableReservation>
<limit>1</limit>
<shares>
<shares>26555</shares>
<level>custom</level>
</shares>
</cpuAllocation>
<memoryAllocation>
<reservation>741</reservation>
<expandableReservation>true</expandableReservation>
<limit>-1</limit>
<shares>
<shares>522240</shares>
<level>custom</level>
</shares>
</memoryAllocation>
</obj>';

CREATE TABLE [dbo].[Resourcepools_5_XML](id int IDENTITY(1,1) NOT NULL, config_spec xml NULL);
INSERT INTO Resourcepools_5_XML VALUES(@xml1),(@xml2)

--(2) to pull that data from your table
--------------------------------------------------------------
;with xmlnamespaces (default 'urn:vim25')
select id, t.c.value('(shares)[1]', 'varchar(200)')
FROM Resourcepools_5_XML x
CROSS APPLY x.config_spec.nodes('(/obj/cpuAllocation/shares, /obj/memoryAllocation/shares)') t(c)



Note that, in this example I created two records and added your id column to show where the records came from:

id   (No column name)
1 16000
1 522240
2 26555
2 522240


-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search