How can I replace a value in xml with .modify from the variable?

Thanks for your help.
I am trying to modify a value within an xml. I found that this can be done w
ith .modify but I must use literal for modify command. I need it variablized
. Is there any way to do it without sp_executesql?
thanks
declare
@.xml varchar(max)
,@.xml1 xml
, @.ConversationHandle char(36)
set @.xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'
SET @.xml1 = @.xml
SET @.ConversationHandle = newid()
This is what I want but using the xml .modify function
select cast(@.xml as xml), cast(REPLACE( @.xml, 'ConversationHandle=""', 'Conv
ersationHandle="' + @.ConversationHandle + '"') as xml)
SET @.xml1.modify('
replace value of (/Tasks/row/@.ConversationHandle)[1]
with "boo"
')
SELECT @.xml1
DECLARE @.m varchar(1000)
SET @.m ='
replace value of (/Tasks/row/@.ConversationHandle)[1]
with "' + cast(@.ConversationHandle as char(36)) + '"'
SET @.xml1.modify(@.m) -- this errors
SELECT @.xml1Hello Farmer,
Off the top of my head, sp_sqlexcutesql is the only was to do this as the
constructor isn't availble in XQuery DML for SQL Server 2005.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks Kent,
I also could not find any better way.
the answer using sp_executesql is:
DECLARE @.SQL nvarchar(max)
SET @.SQL =
'SET @.xml.modify(''replace value of (/Tasks/row/@.ConversationHandle)[1] with
"' + cast(@.ConversationHandle as char(36)) + '"'');'
SELECT @.sql
EXEC sp_executesql
@.stmt = @.sql
,@.params = N'@.xml xml OUTPUT'
,@.xml = @.xml1 OUTPUT
select @.xml1
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741397a8c8d18706916da0@.news.microsoft.com...
> Hello Farmer,
> Off the top of my head, sp_sqlexcutesql is the only was to do this as the
> constructor isn't availble in XQuery DML for SQL Server 2005.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>|||Well you must always use a string literal in the modify method but you can h
ave access to the values in sql columns or sql variables through the use of
sql:column() and sql:variable() in your XQuery.
I would suggest that you read about it in Books Online, but here's a quick e
xample. You can replace the value of your ConversationHandle attribute with
the value from a sql variable named @.handle like this
declare @.xml xml, @.handle char(36)
set @.xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'
SET @.handle = newid()
SET @.xml.modify('
replace value of (/Tasks/row/@.ConversationHandle)[1]
with sql:variable("@.handle")
')
I hope this helps
Denis Ruckebusch
--
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.mi
crosoft.com/info/cpyright.htm
"Farmer" <someone@.somewhere.com> wrote in message news:%23RAddk3AHHA.4428@.TK
2MSFTNGP04.phx.gbl...
Thanks for your help.
I am trying to modify a value within an xml. I found that this can be done w
ith .modify but I must use literal for modify command. I need it variablized
. Is there any way to do it without sp_executesql?
thanks
declare
@.xml varchar(max)
,@.xml1 xml
, @.ConversationHandle char(36)
set @.xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'
SET @.xml1 = @.xml
SET @.ConversationHandle = newid()
This is what I want but using the xml .modify function
select cast(@.xml as xml), cast(REPLACE( @.xml, 'ConversationHandle=""', 'Conv
ersationHandle="' + @.ConversationHandle + '"') as xml)
SET @.xml1.modify('
replace value of (/Tasks/row/@.ConversationHandle)[1]
with "boo"
')
SELECT @.xml1
DECLARE @.m varchar(1000)
SET @.m ='
replace value of (/Tasks/row/@.ConversationHandle)[1]
with "' + cast(@.ConversationHandle as char(36)) + '"'
SET @.xml1.modify(@.m) -- this errors
SELECT @.xml1|||
> Thanks for your help.
> I am trying to modify a value within an xml. I found that this can be done
with .modify but I must use literal for modify command. I need it variabliz
ed. Is there any way to do it without sp_executesql?
> thanks
> declare
> @.xml varchar(max)
> ,@.xml1 xml
> , @.ConversationHandle char(36)
> set @.xml= '<Tasks><row ConversationHandle="" olnID="5981"/></Tasks>'
> SET @.xml1 = @.xml
> SET @.ConversationHandle = newid()
> This is what I want but using the xml .modify function
> select cast(@.xml as xml), cast(REPLACE( @.xml, 'ConversationHandle=""', 'Co
nversationHandle="' + @.ConversationHandle + '"') as xml)
>
> SET @.xml1.modify('
> replace value of (/Tasks/row/@.ConversationHandle)[1]
> with "boo"
> ')
> SELECT @.xml1
> DECLARE @.m varchar(1000)
> SET @.m ='
> replace value of (/Tasks/row/@.ConversationHandle)[1]
> with "' + cast(@.ConversationHandle as char(36)) + '"'
> SET @.xml1.modify(@.m) -- this errors
> SELECT @.xml1
>
>
>
>
> Thanks for your help.
> I am trying to modify a value within an xml. I =
> found that=20
> this can be done with .modify but I must use literal for modify command. =
> I need=20
> it variablized. Is there any way to do it without =
> sp_executesql?
> thanks
> declare
> @.xml varchar(max)
> ,@.xml1 xml
> , @.ConversationHandle char(36)
> set @.xml=3D ''
> SET @.xml1 =3D @.xml
> SET @.ConversationHandle =3D newid()
> This is what I want but using the xml .modify=20
> function
> select cast(@.xml as xml), cast(REPLACE(=20
> @.xml, =
> 'ConversationHandle=3D""', 'ConversationHandle=3D"' + @.ConversationHandle
+ '"') as xml)
> SET @.xml1.modify('
> replace value of (/Tasks/row/@.ConversationHandle)[1]
> with "boo"
> ')
> SELECT @.xml1
> DECLARE @.m varchar(1000)
> SET @.m =3D'
> replace value of (/Tasks/row/@.ConversationHandle)[1]
> with "' + cast(@.ConversationHandle as char(36)) + '"'
> SET @.xml1.modify(@.m) -- this =
> errors
> SELECT =
> @.xml1
You can easily do this using SQL variables in the XQuery literal
SET @.m ='
replace value of (/Tasks/row/@.ConversationHandle)[1]
with sql:variable("@.ConversationHandle")'
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

How can I Passing Table type variable as Output Param

Hi,
How can I pass table type OUTPUT param to a stored procedure ?
regards,> How can I pass table type OUTPUT param to a stored procedure ?
> regards,
Table data type is not allowed for a SP parameter. If you need to use the
rowset returned in T-SQL code, you have two possibilties:
- change the procedure to a table-valued UDF
- save the output in a temp table.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How can I pass a null DataTime variable

How can I pass a null datetime?
Exampl
http://localhost/LUSReportService/RenderPDF.aspx?ReportName=MeetingNoticeMemorandum&RequestedBy=LISSHOC&Legislature=79&Session=R&CalendarDate=6/10/2005%2012:00:00%20AM&CalendarTime=Leave the parameter off
http://localhost/LUSReportService/RenderPDF.aspx?ReportName=MeetingNoticeMemorandum&RequestedBy=LISSHOC&Legislature=79&Session=R&CalendarDate=6/10/2005%2012:00:00%20AM
"Hugo" wrote:
> How can I pass a null datetime?
> Example
> http://localhost/LUSReportService/RenderPDF.aspx?ReportName=MeetingNoticeMemorandum&RequestedBy=LISSHOC&Legislature=79&Session=R&CalendarDate=6/10/2005%2012:00:00%20AM&CalendarTime=
>sql

How can I have a variable number of parameter values in a dataset?

I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter. The problem is I don't know how many. I have a textbox that the user can enter search words in. The select string is built from the string of words that are entered, like this:

For iCount = 0 To UBound(sArray)
strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "
Next

Can I do this is a dataset method? How?

If I can't, what are my options?

Diane

make use of temp table or table variable

declare @.word table

(

word varchar(100)

)

select *

from sometable t inner join @.word w

on t.Description like '%' + w.word + '%'

|||

Hi,

From the question you mentioned, do you want to make a query with multiple input variables? If so, I just want to know how to split them in your solution?

Actually, the user input the keywords in the textbox and they use space on the keyboard to split each word. And when we recieve the request from the user,

we may use split method to separate each words into array.

string s = "keyword1 keyword2 keyword3";
string[] myar = s.Split(' ');

for (int i = 0; i < myar.Length; i++) {

strSQL = strSQL & "Description LIKE '%" & myar(i) & "%' OR "
}

After the sql statement is created, you may put it into the SqlDataAdapter and fill into a DataSet. So you can get the query result which matches the multiple keywords.

SqlDataAdapter myadpt = new SqlDataAdapter(strSQL, myconn);
DataSet myds = new DataSet();
myadpt.Fill(myds);

If this does not answer your question, please feel free to reply. Thank you!

|||

Hi,

You can search more efficiently using 'Contains' in SQL server. For this you need to enable Full-Text search in sql server for a field.

Contains(FieldName, '+''''+@.GroupKeyWord+''''+')'

@.GroupKeyWord is the keyword you can directly pass, i.e. the user input string. See SQL Server help for more details. It is really interesting. If you don't want to use this feature then you can go for split. But split needs a delimiter string, i.e. a single space or a particular character. The resultant array can be looped from lower bound to upper bound to create Sql statement.

The advantage of using contains is its flexibility. User can create Boolean search himself and you just have to pass the value user entered into the textbox directly to the sql statement.

Don't forget to mark this as Answer if this post helps you

|||

KH, you have me totally confused <grin>!

Michael, I'm not having a problem spliting the variables. Say I have a table with fields name, company, address, description. I know how to use a parameter in a strongly typed dataset, say to search for Name=@.name in a method that expects a name parameter. What I need is a method that returns all records where the description field contains one or more of the words the user entered. I can build the select string, but I don't know how to implement this in a dataset method.

Hamlin, this would be optimal. Unfortunately, full text search isn't enabled, and I can't get it enabled. I asked my host, and they won't permit it.

I'm wondering if I should maybe use an SqlDataSource instead of the dataset?

Diane

|||

Hi Mainship,

Sorry for my misunderstanding. Now I know that your problem is focused on how to implement the select string in a dataset method cause you don't know the number of parameter values.

Actually you can use DataSet.TableName.Select(SelectString) Method instead of inputing the select string in the TableAdapter configuration wizard. For example:

northwindDataSet.Products.Select(string);

For more details about DataTable.Select Method ,pls check:http://msdn2.microsoft.com/en-us/library/system.data.datatable.select(VS.80).aspx

If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Thank you!

Diane

How can I get a table reference knowing his name inside a system f

I want to call the system function
DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
parameter a @.TableName varchar variable represanting the table's name.
DBCC CHECKIDENT( ) requires as input the table object, while I have the
table string name.
Is there any way that I can get the table object using its string name
inside the DBCC CHECKIDENT() function ?
example
Create procedure procName (@.TableName varchar)
begin
.......
DBCC CHECKIDENT( theTableObject , RESEED, 100)
......
end;Aigiris, Try using the OBJECT_ID function. From the BOL below. - RLF
OBJECT_ID
Returns the database object identification number.
Syntax
OBJECT_ID ( 'object' )
"Argiris Petromelidis" <Argiris Petromelidis@.discussions.microsoft.com>
wrote in message news:AC4CE899-E310-47C3-9101-B12779C2FDA8@.microsoft.com...
>I want to call the system function
> DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
> parameter a @.TableName varchar variable represanting the table's name.
> DBCC CHECKIDENT( ) requires as input the table object, while I have the
> table string name.
> Is there any way that I can get the table object using its string name
> inside the DBCC CHECKIDENT() function ?
> example
> Create procedure procName (@.TableName varchar)
> begin
> ........
> DBCC CHECKIDENT( theTableObject , RESEED, 100)
> ......
> end;|||Hi
You may want to try using dynamic SQL e.g.
CREATE PROCEDURE MyCheck ( @.objectname sysname )
AS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@.objectname)
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
AND EXISTS ( SELECT * FROM dbo.syscolumns WHERE id = OBJECT_ID(@.objectname)
AND COLUMNPROPERTY(id,name,'IsIdentity') = 1)
BEGIN
DECLARE @.cmd varchar(8000)
SET @.cmd = 'DBCC CHECKIDENT( ''' + QUOTENAME(@.objectname) + ''', RESEED,
100)'
EXEC (@.cmd)
END
John
"Argiris Petromelidis" wrote:

> I want to call the system function
> DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
> parameter a @.TableName varchar variable represanting the table's name.
> DBCC CHECKIDENT( ) requires as input the table object, while I have the
> table string name.
> Is there any way that I can get the table object using its string name
> inside the DBCC CHECKIDENT() function ?
> example
> Create procedure procName (@.TableName varchar)
> begin
> ........
> DBCC CHECKIDENT( theTableObject , RESEED, 100)
> ......
> end;