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 modify a report in Crystal Report 7

I am a Web developer. I am new to VB 6.0 and Crystal Report. I have
joined an organisation where there is an application running(developed
in VB6 and crystal report 7). I have to modify one report. In two
columns I have to make minor changes i.e put and = operator. I am
encountering following problem:

1. When I open the report using Seagate crystal report for rational /
32 bit crystal Report Designer there are three columns which are going
out side the visible area and I am not able to scroll up to them. I
increased the page margin (left / right) and made it to zero and zero
but still I am not able to see those columns. Please guide.
2. Please suggest any good web site for learning Crystal Report 7

Thanks n Regards

Deepak Sinhamicrosoft . public . sqlserver wrote:

Quote:

Originally Posted by

1. When I open the report using Seagate crystal report for rational /
32 bit crystal Report Designer there are three columns which are going
out side the visible area and I am not able to scroll up to them. I
increased the page margin (left / right) and made it to zero and zero
but still I am not able to see those columns. Please guide.

Try setting the zoom level to something less than 100%.|||microsoft . public . sqlserver (dpk.sinha@.gmail.com) writes:

Quote:

Originally Posted by

I am a Web developer. I am new to VB 6.0 and Crystal Report. I have
joined an organisation where there is an application running(developed
in VB6 and crystal report 7). I have to modify one report. In two
columns I have to make minor changes i.e put and = operator. I am
encountering following problem:
>
1. When I open the report using Seagate crystal report for rational /
32 bit crystal Report Designer there are three columns which are going
out side the visible area and I am not able to scroll up to them. I
increased the page margin (left / right) and made it to zero and zero
but still I am not able to see those columns. Please guide.
2. Please suggest any good web site for learning Crystal Report 7

http://www.businessobjects.com/
They also have a web forum, where you probably better odds to get
good answers about Crystal than in an SQL Server forum.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How can I know the data logical file name?

Hi, all
How can i know the the data file .mdf logical file name?
I want to use alter database to modify the file's size.
Thanks!Execute sp_helpfile in the database.
-Sue
On Tue, 19 Aug 2003 16:29:34 -0700, "Hawk"
<abchawk@.hotmail.com> wrote:
>Hi, all
>How can i know the the data file .mdf logical file name?
>I want to use alter database to modify the file's size.
>Thanks!
>|||or
select * from sysfiles
>--Original Message--
>Hi, all
>How can i know the the data file .mdf logical file name?
>I want to use alter database to modify the file's size.
>Thanks!
>
>.
>

How Can I get my sproc to return records with 0 as ClientID

Can anyone help me modify this sproc's Where clause or Joins to let (T) task records with a 0 to be returned?

If I enter a ClientID I want to return only those task records with the ClientID I entered (this works).

If I enter no ClientID I want to return all task records, even those with a 0 in the ClientID field.

ALTER PROCEDURE dbo.CMAdmin

@.SID int

AS
SELECT
A.CompanyName,
C.FirstName,
C.LastName,
C.ClientID,Convert(varchar(10),
T.ActionDate, 10) AS [Action Date],
T.Priority,
T.Status,
T.Subject,
T.Note,
T.CompletionDate, 10) AS Completed,
T.DateEntered AS Entered,
T.EnteredBy AS [Entered By],

CASE
WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN
C.[FirstName] +' '+ C.[LastName]
ELSE A.[CompanyName]
END AS DRName

FROM
tblClients C LEFT OUTER JOIN tblClientAddresses A
ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T
ON C.ClientID = T.ClientID

WHERE
C.ClientID = Isnull(@.SID,C.ClientID)

hi

u can modify the where close like this

ALTER PROCEDURE dbo.CMAdmin

@.SID int

AS
SELECT
A.CompanyName,
C.FirstName,
C.LastName,
C.ClientID,Convert(varchar(10),
T.ActionDate, 10) AS [Action Date],
T.Priority,
T.Status,
T.Subject,
T.Note,
T.CompletionDate, 10) AS Completed,
T.DateEntered AS Entered,
T.EnteredBy AS [Entered By],

CASE
WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN
C.[FirstName] +' '+ C.[LastName]
ELSE A.[CompanyName]
END AS DRName

FROM
tblClients C LEFT OUTER JOIN tblClientAddresses A
ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T
ON C.ClientID = T.ClientID

WHERE
C.ClientID = COALESCE(@.SID,C.ClientID)

this query will return all records when client id is null and when it is not null it only returns rows specified in the where condition

for more details

http://www.sqlteam.com/item.asp?ItemID=2077

regards