Re: XMLQuery equivalent of SELECT [columnnameA] WHERE [columnnameB] IN
- From: "rshillington" <rshillington@xxxxxxxxx>
- Date: 12 Apr 2007 19:21:52 -0700
If I understand your request correctly, you have a multi-valued column
(using the XML data type) and you have a multi-value parameter as
input. If that's the case then you'll need the nodes and value method
of the XML type to shred the input parameter into a relation, and then
again the nodes and value method along with CROSS APPLY to make a
relation out of the multi-value column.
I have a demonstration on my community site (http://www.ifoundtime.com/
community ) that illustrates this very requirement. It walks through
a table of people and their favourite colours, stored as an xml
column. It then takes an input parameter (in this case declared
simply as a variable of type XML with set to a literal value) and
queries for people who have a favourite colour in the list of
favourite colours. The sample queries are supplied there. Just look
for XmlCrossApply in the list of SQL Server videos.
Hope that helps,
Ralph Shillington
http://www.ifoundtime.com/community
On Apr 11, 10:20 pm, etech <e...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I am new to XMLQuery, and review of the best practices and examples has yet
to yield an example of what I am trying to do:
Given a table containing an untyped XML column in which multiple nodes could
exist:
<ThingTypes><value id="8" /><value id="17" /><value id="22" /></ThingTypes>
And a multi-valued XML parameter passed in to a stored procedure,
<ThingTypes><value id="8" /><value id="17" /></ThingTypes>
How does one construct a query to:
"Return rows in table A in which ONE OR MORE node values in the input
parameter are found in the xml column of Table A"
This is sort of like the SQL query:
SELECT [columnA] FROM TableA
WHERE
TableA.[columnB] IN
SELECT ([columnC] FROM TableB WHERE [criteria])
Is there a method to do an "exist()" on an xml field where multiple nodes
could exist?
Thanks.
.
- Follow-Ups:
- Prev by Date: Re: Can WordML be put in a SQL Server XML column?
- Next by Date: Re: XMLQuery equivalent of SELECT [columnnameA] WHERE [columnnameB
- Previous by thread: Dynamic attributes based on column values
- Next by thread: Re: XMLQuery equivalent of SELECT [columnnameA] WHERE [columnnameB
- Index(es):
Relevant Pages
|
