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

Help needed in Table partition

Hi,
I'm doing horizontal partition with partition key as an
integer datatype.It's a part of primary key also.When i
search for a particular value, it's fetching correct
results ,but if we examine execution plan all the
partitions are searched for a particular search.
Please help me to fetch the correct result in execution
plan
Thanks in advance,
Sunish
is your primary key clustered? Are you saying the the execution plan does a
table scan?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Sunish" <sunish_007@.hotmail> wrote in message
news:732801c4761d$d3435170$a501280a@.phx.gbl...
> Hi,
> I'm doing horizontal partition with partition key as an
> integer datatype.It's a part of primary key also.When i
> search for a particular value, it's fetching correct
> results ,but if we examine execution plan all the
> partitions are searched for a particular search.
> Please help me to fetch the correct result in execution
> plan
> Thanks in advance,
> Sunish
|||hi,
yea, the primary key is a clustered one.When i checked
the execution p[lan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||I think this is normal.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<anonymous@.discussions.microsoft.com> wrote in message
news:873601c47855$f7adc3d0$a601280a@.phx.gbl...[vbcol=seagreen]
> hi,
> yea, the primary key is a clustered one.When i checked
> the execution p[lan it shows Clustered Index Scan for
> each partition ......also instead of compute saclar it's
> showing Concatenation ,Cost=2%.
> Thanks in advance
> Sunish
>
> execution plan does a
|||hi,
Since it has to search for the corresponding partition
only rather than searching the entire partition views ,i
think i am wrong some where .
also in the execution plan ,it has to show compute scalar
instead of concatenation, as i referred to articles which
i collected from the net.
please inform if there is any other way i need to perform
this .
my query is like this only
select * from detailstable where programcode =222
[vbcol=seagreen]
>--Original Message--
>I think this is normal.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:873601c47855$f7adc3d0$a601280a@.phx.gbl...
it's[vbcol=seagreen]
as an[vbcol=seagreen]
also.When i[vbcol=seagreen]
execution
>
>.
>