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

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/

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 (AT) nospam-online (DOT) sbcglobal.net> wrote

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/

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 (AT) nospam-online (DOT) sbcglobal.net> wrote