Prev: Visual Foxpro
Next: Permission issue with LINQ ?
From: Charles on 3 Aug 2010 18:45
I have something like the following:

declare @msg xml

set @msg = '<Message><SubItem>Some Text</SubItem></Message>'

I'd like to be able to extract the value of the SubItem node, but I can't
find the syntax. It might be something like (I made this up):

SELECT x.SubItem
FROM @msg

and it would return the single row

SubItem
----------------
Some Text

Can anyone tell me what I should write?

TIA

Charles


From: Dan Guzman on 3 Aug 2010 21:17
> declare @msg xml
>
> set @msg = '<Message><SubItem>Some Text</SubItem></Message>'
>
> I'd like to be able to extract the value of the SubItem node, but I can't
> find the syntax. It might be something like (I made this up):
>
> SELECT x.SubItem
> FROM @msg
>
> and it would return the single row
>
> SubItem
> ----------------
> Some Text

One method:

DECLARE @msg xml;
SET @msg = '<Message><SubItem>Some Text</SubItem></Message>';
SELECT @msg.query('/Message/SubItem[1]').value('.', 'varchar(100)') AS
SubItem;


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/



From: Charles on 4 Aug 2010 05:03
Hi Dan

Thanks very much for the reply. That's exactly what I needed.

I also found that I can omit the [1] and it still works, perhaps because I
only have one element called SubItem?

Cheers

Charles


"Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message
news:AFACC475-6D5D-4FDC-8E63-7C0CE1B281CA(a)microsoft.com...
>> declare @msg xml
>>
>> set @msg = '<Message><SubItem>Some Text</SubItem></Message>'
>>
>> I'd like to be able to extract the value of the SubItem node, but I can't
>> find the syntax. It might be something like (I made this up):
>>
>> SELECT x.SubItem
>> FROM @msg
>>
>> and it would return the single row
>>
>> SubItem
>> ----------------
>> Some Text
>
> One method:
>
> DECLARE @msg xml;
> SET @msg = '<Message><SubItem>Some Text</SubItem></Message>';
> SELECT @msg.query('/Message/SubItem[1]').value('.', 'varchar(100)') AS
> SubItem;
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
>
>
From: Dan Guzman on 4 Aug 2010 08:12
> I also found that I can omit the [1] and it still works, perhaps because I
> only have one element called SubItem?

Only the first SubItem will be returned due to the ordinal specification.
If you omit the ordinal and have multiple SubItem elements, the text of all
of the SubItem elements will be concatenated. The example below uses the
XML nodes method as an alternative. This will shred SubItem elements
individually. You can also use this as alternative to the XML query method
I posted originally even if you have only one SubItem.

DECLARE @msg xml;
SET @msg = '<Message><SubItem>Some Text1</SubItem><SubItem>Some
Text2</SubItem></Message>';
SELECT SubItem.query('./text()')
FROM @msg.nodes('/Message/SubItem') AS Message(SubItem);

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/



From: Charles on 4 Aug 2010 08:59
That's very helpful, thanks Dan. I'll have an experiment with the two
methods and decide which is going to work best in my situation.

Cheers

Charles


"Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message
news:F5E6EC2F-F18A-4C1F-8218-7A7391485EE8(a)microsoft.com...
>
>> I also found that I can omit the [1] and it still works, perhaps because
>> I only have one element called SubItem?
>
> Only the first SubItem will be returned due to the ordinal specification.
> If you omit the ordinal and have multiple SubItem elements, the text of
> all of the SubItem elements will be concatenated. The example below uses
> the XML nodes method as an alternative. This will shred SubItem elements
> individually. You can also use this as alternative to the XML query
> method I posted originally even if you have only one SubItem.
>
> DECLARE @msg xml;
> SET @msg = '<Message><SubItem>Some Text1</SubItem><SubItem>Some
> Text2</SubItem></Message>';
> SELECT SubItem.query('./text()')
> FROM @msg.nodes('/Message/SubItem') AS Message(SubItem);
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
>
>
 | 
Pages: 1
Prev: Visual Foxpro
Next: Permission issue with LINQ ?