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