Dynamic search with xml?

We have a table like this:
CREATE TABLE [Account](
[ID] [int] NULL,
[Name] [nvarchar](20) NULL,
[Status] [nvarchar](20) NULL
)
We have an xml file that defines search parameters that looks like
this:
<SearchParms>
<Parm col="ColToSearchOn" val="ValToSearchFor"/>
</SearchParms>
The basic idea is to create a stored procedure that accepts an xml doc
as parameter and then searchs the account table based on the parameters
passed in the xml file.
For example, if this is the doc passed into the sproc:
<SearchParms>
<Parm col="ID" val="123"/>
</SearchParms>
Then the sproc will eval something like:
SELECT *
FROM ACCOUNT
WHERE ID = 123
If the doc looks like this:
<SearchParms>
<Parm col="ID" val="123"/>
<Parm col="Name" val="Straus, LLC"/>
</SearchParms>
Then the sproc will eval something like:
SELECT *
FROM ACCOUNT
WHERE ID = 123
AND Name = 'Straus, LLC'
So, I'm wondering if there is a way to create a join between the xml
file and the relational table that will return the results I'm wanting.
I was hoping to be able to utilize some fancy XQuery type of stuff
rather than dynamically build a string based on the xml and then run
the sp_executesql method on the string.
I'm open to suggestions on xml format. I've only included the above as
an example. Ideas? Thanks for any help.Something like,
select * from account
where
id in
(
select a.b.value('(.)[1]', 'int')
from @.xml.nodes('/descendant::@.val')
)
Pohwan Han. Seoul. Have a nice day.
"Chris Kilmer" <christopherkilmer@.gmail.com> wrote in message
news:1138404194.372843.253320@.f14g2000cwb.googlegroups.com...
> We have a table like this:
> CREATE TABLE [Account](
> [ID] [int] NULL,
> [Name] [nvarchar](20) NULL,
> [Status] [nvarchar](20) NULL
> )
>
> We have an xml file that defines search parameters that looks like
> this:
> <SearchParms>
> <Parm col="ColToSearchOn" val="ValToSearchFor"/>
> </SearchParms>
> The basic idea is to create a stored procedure that accepts an xml doc
> as parameter and then searchs the account table based on the parameters
> passed in the xml file.
> For example, if this is the doc passed into the sproc:
> <SearchParms>
> <Parm col="ID" val="123"/>
> </SearchParms>
> Then the sproc will eval something like:
> SELECT *
> FROM ACCOUNT
> WHERE ID = 123
> If the doc looks like this:
> <SearchParms>
> <Parm col="ID" val="123"/>
> <Parm col="Name" val="Straus, LLC"/>
> </SearchParms>
> Then the sproc will eval something like:
> SELECT *
> FROM ACCOUNT
> WHERE ID = 123
> AND Name = 'Straus, LLC'
> So, I'm wondering if there is a way to create a join between the xml
> file and the relational table that will return the results I'm wanting.
> I was hoping to be able to utilize some fancy XQuery type of stuff
> rather than dynamically build a string based on the xml and then run
> the sp_executesql method on the string.
> I'm open to suggestions on xml format. I've only included the above as
> an example. Ideas? Thanks for any help.
>|||If your column names would be fixed, Han's suggestion may work.
However, if you need to create different predicates and you query against a
relational table, you either have to make the table into an XML datatype
itself (e.g using FOR XML) and then use local-name() to compare against the
col attribute or use dynamic SQL.
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OJBUABxJGHA.1320@.TK2MSFTNGP15.phx.gbl...
> Something like,
> select * from account
> where
> id in
> (
> select a.b.value('(.)[1]', 'int')
> from @.xml.nodes('/descendant::@.val')
> )
> --
> Pohwan Han. Seoul. Have a nice day.
> "Chris Kilmer" <christopherkilmer@.gmail.com> wrote in message
> news:1138404194.372843.253320@.f14g2000cwb.googlegroups.com...
>sql