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

Dynamic Crosstab

Say I have the following output from a view on SQL Server 2000;
RegID StudID SeqNo EnrolNo Name Status
Prog AttendDate AttendCode
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 8/8/2005 X
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 15/8/2005 X
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 22/8/2005 O
How do I massage this using a Stored Procedure into the following output?
RegID StudID SeqNo EnrolNo Name Status
Prog 8/8/2005 15/8/2005 22/8/2005
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A X X O
Bearing in mind that the date columns are dynamic and can be anything from 0
to 18.
I have found some scripts but I couldn't get the mto work with dates :(
Thanks.See if these help.
Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Mark Parter" wrote:

> Say I have the following output from a view on SQL Server 2000;
> RegID StudID SeqNo EnrolNo Name Status
Prog AttendDate AttendCode
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 8/8/2005 X
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 15/8/2005 X
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 22/8/2005 O
> How do I massage this using a Stored Procedure into the following output?
> RegID StudID SeqNo EnrolNo Name Status
Prog 8/8/2005 15/8/2005 22/8/200
5
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A X X O
> Bearing in mind that the date columns are dynamic and can be anything from
0
> to 18.
> I have found some scripts but I couldn't get the mto work with dates :(
> Thanks.