Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reading XML from column Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 2:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:56 AM
Points: 15, Visits: 251
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







Post #1462930
Posted Saturday, June 15, 2013 12:32 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:54 AM
Points: 2,611, Visits: 1,640
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..
Post #1463888
Posted Monday, June 17, 2013 1:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:56 AM
Points: 15, Visits: 251
It worked!

Thanks so much. Really appreciate it :)

Cheers!
Post #1464014
Posted Monday, June 17, 2013 1:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:54 AM
Points: 2,611, Visits: 1,640
No problem... thanks for the feedback :)
Post #1464018
Posted Wednesday, October 9, 2013 2:45 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 8:06 PM
Points: 581, Visits: 2,711
cor_perlee (6/17/2013)
It worked!

Thanks so much. Really appreciate it :)

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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1503309
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse