Extract values from XML in a Strored Proc

I pass an xml to a Stored Proc similar to below.
How do I extaract the id and value from below XML?
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value><value>SupplierRelations</value></MnemonicIDs>'
SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
T.MnemonicIDs.value('/id', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
You probably want:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
<id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value>
<value>SupplierRelations</value>
</MnemonicIDs>'
SELECT T.MnemonicIDs.value('.', 'VARCHAR(50)'),
T.MnemonicIDs.value('(for $v in . return ../id[. << $v])[last()]',
'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs/value') AS T(MnemonicIDs)
However, if you can change the XML structure to something like:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>MarketPowerInfluence</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>IndustryStanding</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>SupplierRelations</value>
</MnemonicIDs>'
then the query becomes simpler:
SELECT T.MnemonicIDs.value('value[1]', 'VARCHAR(50)'),
T.MnemonicIDs.value('id[1]', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
Best regards
Michael
"C" <C@.discussions.microsoft.com> wrote in message
news:BCFF9712-A442-42E9-9F7F-E686F65137D0@.microsoft.com...
>I pass an xml to a Stored Proc similar to below.
> How do I extaract the id and value from below XML?
>
> DECLARE @.piMnemonicIDs xml
> SET @.piMnemonicIDs =
> '<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value><value>SupplierRelations</value></MnemonicIDs>'
> SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
> T.MnemonicIDs.value('/id', 'VARCHAR(50)')
> FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
>

Extract values from XML in a Strored Proc

I pass an xml to a Stored Proc similar to below.
How do I extaract the id and value from below XML?
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerInfl
uence</value><value>IndustryStanding</value><value>SupplierRelations</value>
</MnemonicIDs>'
SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
T.MnemonicIDs.value('/id', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)You probably want:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
<id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value>
<value>SupplierRelations</value>
</MnemonicIDs>'
SELECT T.MnemonicIDs.value('.', 'VARCHAR(50)'),
T.MnemonicIDs.value('(for $v in . return ../id[. << $v])[last()]',
'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs/value') AS T(MnemonicIDs)
However, if you can change the XML structure to something like:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>MarketPowerInfluence</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>IndustryStanding</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>SupplierRelations</value>
</MnemonicIDs>'
then the query becomes simpler:
SELECT T.MnemonicIDs.value('value[1]', 'VARCHAR(50)'),
T.MnemonicIDs.value('id[1]', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
Best regards
Michael
"C" <C@.discussions.microsoft.com> wrote in message
news:BCFF9712-A442-42E9-9F7F-E686F65137D0@.microsoft.com...
>I pass an xml to a Stored Proc similar to below.
> How do I extaract the id and value from below XML?
>
> DECLARE @.piMnemonicIDs xml
> SET @.piMnemonicIDs =
> '<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerIn
fluence</value><value>IndustryStanding</value><value>SupplierRelations</valu
e></MnemonicIDs>'
> SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
> T.MnemonicIDs.value('/id', 'VARCHAR(50)')
> FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
>