Reading XML from column

Question

Reading XML from column

cor_perlee

Ten Centuries

Points: 1059
More actions
June 13, 2013 at 2:12 am

#98819

Hello all,
This question has probably been asked before but I just can't figure out what to do.
I have a table:
-------------------------------------------------------------------------------------------------------------------------------------------------
[font="Arial"]/****** 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[/font]
-------------------------------------------------------------------------------------------------------------------------------------------------
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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Arthur Olcot SSCertifiable Points: 6564 More actions · Answer 1

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 Ten Centuries Points: 1059 More actions · Answer 2

It worked!

Thanks so much. Really appreciate it 🙂

Cheers!

Arthur Olcot SSCertifiable Points: 6564 More actions · Answer 3

Arthur Olcot

SSCertifiable

Points: 6564

June 17, 2013 at 1:54 am

#1624484

No problem... thanks for the feedback 🙂

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

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='

<objxsi: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='

<objxsi: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='

<objxsi: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)

116000

1522240

226555

2522240

"I cant 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."

-- Itzik Ben-Gan 2001