Help newbie with quering XML

Hello,
I have XML data which I receive from a vendor and store in a table
with the XML datatype in this arrangement:
<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>
How do I query for item1 and return just, widget ?
TIA,
RichHi,
How about something like this
DECLARE @.x XML
SET @.x =
'<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>'
SELECT @.x.value('(//ITEM[@.name="item1"]/@.value)[1]','nvarchar(MAX)')
If you want to parameterize the value for the name attribute, you can
use sql:variable() and do this
DECLARE @.x XML
SET @.x =
'<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>'
DECLARE @.n nvarchar(MAX)
SET @.n = 'item1'
SELECT
@.x.value('(//ITEM[@.name=sql:variable("@.n")]/@.value)[1]','nvarchar(MAX)')
I hope this helps
Denis Ruckebusch
XML datatype test team
--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sk8man31" <me@.aol.com> wrote in message
news:rc71825rqgo28vr6dr18beb0bn1jv148qe@.
4ax.com...
> Hello,
> I have XML data which I receive from a vendor and store in a table
> with the XML datatype in this arrangement:
> <MYXML>
> <ITEMS>
> <ITEM name="item1" value="widget" />
> <ITEM name="item2" value="dongle" />
> <ITEM name="item3" value="thingy" />
> </ITEMS>
> </MYXML>
> How do I query for item1 and return just, widget ?
> TIA,
> Rich|||On Fri, 2 Jun 2006 18:18:53 -0700, "Denis Ruckebusch [MSFT]"
<denisruc@.online.microsoft.com> wrote:

>Hi,
> How about something like this
>DECLARE @.x XML
>SET @.x =
>'<MYXML>
><ITEMS>
><ITEM name="item1" value="widget" />
><ITEM name="item2" value="dongle" />
><ITEM name="item3" value="thingy" />
></ITEMS>
></MYXML>'
>SELECT @.x.value('(//ITEM[@.name="item1"]/@.value)[1]','nvarchar(MAX)')
>
>If you want to parameterize the value for the name attribute, you can
>use sql:variable() and do this
>DECLARE @.x XML
>SET @.x =
>'<MYXML>
><ITEMS>
><ITEM name="item1" value="widget" />
><ITEM name="item2" value="dongle" />
><ITEM name="item3" value="thingy" />
></ITEMS>
></MYXML>'
>DECLARE @.n nvarchar(MAX)
>SET @.n = 'item1'
>SELECT
>@.x.value('(//ITEM[@.name=sql:variable("@.n")]/@.value)[1]','nvarchar(MAX)')
>
>I hope this helps
>Denis Ruckebusch
>XML datatype test team
Denis,
Thanks for your help, that worked great!
~Rich