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 FLOWR XQuery into an xml variable

Hello.
I'm trying to do something I haven't seen anywhere, and have been
trying everything but no joy. Basically, my XML is in a variable, and
I'm querying that variable to filter it further. The problem is that
these filters are dynamic. I want to keep the results as an xml
variable because I want to work with them further after this query (I
will do an xpath query so I can use position() and return a page). I'd
really like to stay away from using a tmp table for this.
See the code inline for a few of the things I've tried. I appreciate
any help on this...
Thanks very much in advance for anyone taking the time to help.
Relevant Code with sample xml filling the var
This should be ready to run (and break on the dynamic attempts)
Move the close comments (*/) around to try one method at a time
---
DECLARE @.ItemXML xml
SET @.ItemXML = '
<itemdata>
<item>
<rank>558</rank>
<itemid>11111111</itemid>
<catid>1</catid>
<pid>11</pid>
<ctid>1</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 1</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #1</provider>
</item>
<item>
<rank>558</rank>
<itemid>22222222</itemid>
<catid>22</catid>
<pid>50</pid>
<ctid>2</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 2</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #2</provider>
</item>
<item>
<rank>558</rank>
<itemid>333333333</itemid>
<catid>33</catid>
<pid>50</pid>
<ctid>3</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 3</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #3</provider>
</item>
</itemdata>'
DECLARE @.CatID int, @.ProviderID int, @.CTID int
-- JUST SET 1 VAR FOR NOW, BUT THIS IS BUILT TO ALLOW MULTIPLE FILTERS
SET @.CTID = 2
DECLARE @.ItemStmt varchar(500), @.XQuery varchar(300), @.WhereStmt
varchar(200)
SET @.WhereStmt = 'where 1 = 1'
IF @.CatID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/catid[1] =
'+CONVERT(varchar(4),@.CatID)
IF @.ProviderID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/pid[1] =
'+CONVERT(varchar(4),@.ProviderID)
IF @.CTID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/ctid[1] =
'+CONVERT(varchar(4),@.CTID)
/*
Base Query with no dynamic vars... this works
*/
SET @.ItemXML = @.ItemXML.query('<itemdata>{
for $i in itemdata[1]/item
where 1 = 1 and $i/ctid[1] = 2
return $i
}</itemdata>
')
SELECT 'Attempt #1: Works',@.ItemXML
/*
Attempt #2... sort of like mrorke at
http://blogs.msdn.com/mrorke/archiv.../24/484237.aspx
EXEC the entire SET @.Var statement.
This results in "Must declare the scalar variable "@.ItemXML"."
-- move close comment here:
SET @.XQuery = '<itemdata>{
for $i in itemdata[1]/item
'+@.WhereStmt+'
return $i
}
</itemdata>'
SET @.XQuery = 'SET @.ItemXML = @.ItemXML.query('''+@.XQuery+''')'
EXEC(@.XQuery)
SELECT 'Attempt #2: Breaks', @.ItemXML
*/
/*
Attempt #3... Try a sql:variable inline
Result: XQuery [query()]: Syntax error near 'sql', expected 'where',
'(stable) order by' or 'return'.
-- move close comment here:
SET @.ItemXML = @.ItemXML.query('<itemdata>{
for $i in itemdata[1]/item
sql:variable("@.WhereStmt")
return $i
}
</itemdata>')
SELECT 'Attempt #3: Breaks', @.ItemXML
*/Rather than using EXEC(@.XQuery), try using this
EXEC sp_executeSQL @.XQuery,N'@.ItemXML XML OUTPUT',@.ItemXML OUTPUT
You'll have to declare @.XQuery as nvarchar instead of varchar|||Markc. Thank you very much for your response. That did exactly what I
needed it to do.
One problem I'm seeing now is in my next step where I apply an xpath to
the @.ItemXML just to do the pagination. It seems that xpath step then
accounts for 85% of my query cost. I'm trying another approach just to
be sure I've covered all my bases and can get the most performant
solution, and I'm running into trouble because I'm still learning the
xml datatype & some other SQL 2005 features. Anyway, I though I'd try a
query against the xml variable, while also joining to my lookup tables
and creating a rownumber for pagination. I've tried several different
syntax with this, but it keeps breaking with "Invalid object name 'R'",
so if anyone can see what I'm doing wrong, again, I'd greatly
appreciate it.
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS RowNumber,
i.value('rank','int'),
i.value('itemid','int'),
i.value('catid','int'),
dbo.c.vch_categorytype AS cat,
i.value('pid','int'),
dbo.p.vch_providername_public AS provider,
i.value('ctid','int'),
i.value('link','varchar(300)'),
i.value('thumburl','varchar(300)'),
i.value('title','varchar(500)'),
i.value('published','datetime')
FROM R cross apply @.ItemXML.nodes('itemdata') R(i)
JOIN Categories_LU c ON R.i.value('catid','int') = c.i_category_id
JOIN Providers_LU p ON R.i.value('pid','int') = p.i_provider_id
WHERE RowNumber BETWEEN 1 AND 20 -- assume page 1, items 1-20
AND R.i.value('ctid','int') = 1 -- this would eventually be a dynamic
where clause|||I think
FROM R cross apply @.ItemXML.nodes('itemdata') R(i)
should be
FROM @.ItemXML.nodes('itemdata') R(i)
However, I believe there are other problems here as well. Suggest you
post the DDL for the two tables and some sample data.
Regards
Mark|||Sorry... I know @.ItemXML.nodes('itemdata') won't return the nodes... I
was thrashing around trying different things. Here are some other ways
I've tried to get this...
FROM R cross apply @.ItemXML.nodes('itemdata/item') R(i)
FROM FooTblNm cross apply @.ItemXML.nodes('itemdata/item') R(i)
... also have mixed up R.i.value and i.value... Nothing made a
difference. Always came back with the table ("R", "FooTblNm") being an
invalid object. Sure, I know answer #1 is I need to read a book so I
don't have to guess on syntax. :-)
Thanks again,
STA|||Here's a complete query with tmp table data...
Again, thanks for your help.
----
--
DECLARE @.ItemXML xml
SET @.ItemXML = '
<itemdata>
<item>
<rank>558</rank>
<itemid>11111111</itemid>
<catid>1</catid>
<pid>11</pid>
<ctid>1</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 1</title>
<published>2006-04-05T15:52:17</published>
</item>
<item>
<rank>558</rank>
<itemid>22222222</itemid>
<catid>22</catid>
<pid>50</pid>
<ctid>2</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 2</title>
<published>2006-04-05T15:52:17</published>
</item>
<item>
<rank>558</rank>
<itemid>333333333</itemid>
<catid>33</catid>
<pid>50</pid>
<ctid>3</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 3</title>
<published>2006-04-05T15:52:17</published>
</item>
</itemdata>'
-- CHECK TO SEE IF #tmps ARE THERE ALREADY...
-- THE PROBABLY DIDN'T GET DROPPED BECAUSE THE QUERY BROKE
IF object_id('tempdb..#tmpCats') IS NULL
BEGIN
CREATE TABLE #tmpCats (i_category_id int, vch_category
varchar(100))
INSERT #tmpCats VALUES (1,'Category 1')
INSERT #tmpCats VALUES (2,'Category 2')
INSERT #tmpCats VALUES (3,'Category 3')
END
IF object_id('tempdb..#tmpProviders') IS NULL
BEGIN
CREATE TABLE #tmpProviders (i_provider_id int, vch_provider
varchar(100))
INSERT #tmpProviders VALUES (1,'Provider 1')
INSERT #tmpProviders VALUES (2,'Provider 2')
INSERT #tmpProviders VALUES (3,'Provider 3')
END
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS RowNumber,
i.value('rank','int'),
i.value('itemid','int'),
i.value('catid','int'),
c.vch_category AS cat,
i.value('pid','int'),
p.vch_provider AS provider,
i.value('ctid','int'),
i.value('link','varchar(300)'),
i.value('thumburl','varchar(300)'),
i.value('title','varchar(500)'),
i.value('published','datetime')
FROM R cross apply @.ItemXML.nodes('itemdata/item') R(i)
JOIN #tmpCats c ON R.i.value('catid','int') = c.i_category_id
JOIN #tmpProviders p ON R.i.value('pid','int') = p.i_provider_id
WHERE RowNumber BETWEEN 1 AND 20 -- assume page 1, items 1-20
AND R.i.value('ctid','int') = 1 -- eventually a dynamic where clause
DROP TABLE #tmpCats
DROP TABLE #tmpProviders|||This should help get you started
;
WITH
XMLNodes(RowNumber,rank,itemid,catid,cat
,pid,provider,ctid,link,thumburl,tit
le,published)
AS(
SELECT ROW_NUMBER() OVER (ORDER BY i.value('rank[1]','int') DESC) AS
RowNumber,
i.value('rank[1]','int'),
i.value('itemid[1]','int'),
i.value('catid[1]','int'),
c.vch_category,
i.value('pid[1]','int'),
p.vch_provider,
i.value('ctid[1]','int'),
i.value('link[1]','varchar(300)'),
i.value('thumburl[1]','varchar(300)'),
i.value('title[1]','varchar(500)'),
i.value('published[1]','datetime')
FROM @.ItemXML.nodes('/itemdata/item') R(i)
JOIN #tmpCats c ON R.i.value('catid[1]','int') = c.i_category_id
JOIN #tmpProviders p ON R.i.value('pid[1]','int') = p.i_provider_id
WHERE R.i.value('ctid[1]','int') = 1
)
SELECT RowNumber,
rank,
itemid,
catid,
cat,
pid,
ctid,
link,
thumburl,
title,
published
FROM XMLNodes
WHERE RowNumber BETWEEN 1 AND 20|||mark,
You absolutely rock! Thank you so much for your help on this.
STA

Dynamic connection string in dataset?

Hi,

Since rds (dataset file) is simple XML, is it possible to change the datasource value from it and use it without deploying it on the RS server, similar as we do with the web.config?

TIA,

Tanmaya

Just changing the xml contents of a rds file in the file system won't have any effects on the report server. Shared data source settings can only be changed through the RS SOAP API methods.

-- Robert

Dynamic attribute name

Hi,

I've tried following XQuery:

Code Snippet

declare @.rowoldXML xml;

declare @.attrValue nvarchar(max), @.cursorValue nvarchar(max);

declare @.s nvarchar(max);

SET @.rowoldXML = '<rowold Text="Hello"/>';

SET @.attrValue = '1.59'

SET @.cursorValue = 'Price';

SET @.rowoldXML.modify( 'insert attribute sql:variable("@.cursorValue"){sql:variable("@.attrValue")} into (/rowold)[1]' );

which gave me the error message : "XQuery [modify()]: "{" was expected."
Is there a way to dynamically name the attribute ( in my little sample @.cursorValue ) ?

TIA,

Karsten

It's not supported. The attribute or element name in the modify method must be constant.

You can use dynamic T-SQL to do this if you want.

dynamic (xml text fields) for update sproc

hi folks
anyone have a link or sproc for doing (simple table) updates where the field
list for the update is dynamically created from the inbound XMLtext (could
either validate against an XMLSchema or sysColumns)?
for instance (this is out of Pubs):
CREATE PROCEDURE dbo.sp_update_employee
@.xmltext ntext
AS
-- sample
-- '<update><Employee emp_id="GHT50241M" lname="Thomas II"
fname="Larry"/></update>'
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmltext
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@.hDoc, 'update/Employee')
WITH Employee XMLEmployee
WHERE Employee.emp_id = XMLEmployee.emp_id
EXEC sp_xml_removedocument @.hDoc
--SELECT * from Employee WHERE emp_id='GHT50241M' FOR XML AUTO, elements
GO
I'd like to have the updating happen only to those elements/fields existing
in the XML. (In a time crunch, or I'd start writing my own.)
Rob
A couple of options:
Check out SqlXml updategrams, it will do this for you automatically.
Probably the easiest way to do with OpenXml is shred the document into edge
table format and then build up the sql yourself.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

dynamic (xml text fields) for update sproc

hi folks
anyone have a link or sproc for doing (simple table) updates where the field
list for the update is dynamically created from the inbound XMLtext (could
either validate against an XMLSchema or sysColumns)?
for instance (this is out of Pubs):
CREATE PROCEDURE dbo.sp_update_employee
@.xmltext ntext
AS
-- sample
-- '<update><Employee emp_id="GHT50241M" lname="Thomas II"
fname="Larry"/></update>'
--
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmltext
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@.hDoc, 'update/Employee')
WITH Employee XMLEmployee
WHERE Employee.emp_id = XMLEmployee.emp_id
EXEC sp_xml_removedocument @.hDoc
--SELECT * from Employee WHERE emp_id='GHT50241M' FOR XML AUTO, elements
GO
I'd like to have the updating happen only to those elements/fields existing
in the XML. (In a time crunch, or I'd start writing my own.)
RobA couple of options:
Check out SqlXml updategrams, it will do this for you automatically.
Probably the easiest way to do with OpenXml is shred the document into edge
table format and then build up the sql yourself.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad