Solved

Select REPLACE in XML

Posted on 2008-10-16
8
934 Views
Last Modified: 2010-04-21
I'm not sure if this is do-able.

I'm doing a select on a result set that is XML data.
select replace([theDate], 'images1', 'images') from theTemp

I need to do it with a replace statement and am getting
Argument data type xml is invalid for argument 1 of replace function.
0
Comment
Question by:lrbrister
  • 4
  • 4
8 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22730312
http://technet.microsoft.com/en-us/library/ms345122.aspx#sql2k5_xqueryintro_topic13

See the above.  You will need to use XML methods like XQuery example in link like:
[theDate].replace(...)

Hope this helps.
0
 

Author Comment

by:lrbrister
ID: 22730392
mwvisa1:,
  I am way out of my depth here.  You have a select statement example?
For XQuery or XML methods you may as well be speeking ancient Aramaic to me.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22730642
Sorry about that.  I needed a little more coffee in me anyway.  Out west right now, so been up early in this time zone. :)

Do something like this function:
(maybe there is another way, but with the method I suggested it doesn't like mutating the XML right in the select as it uses a SET command; therefore, I first create the below in code snippet then usage is as below)

SELECT dbo.fn_ChangeXMLText(theData, 'images1', 'images')
FROM theTemp
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_ChangeXMLText]
(
	@theXml XML,
    @oldText nvarchar(50),
    @newText nvarchar(50)
)
RETURNS XML
AS
BEGIN
	SET @theXml.modify('replace value of (//text()[.=sql:variable("@oldText")])[1] with sql:variable("@newText")')
	RETURN @theXml
END

Open in new window

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:lrbrister
ID: 22730966
mwvisa1:
I set up some variables and ran it below.  No replace is taking place.

----CODE----
declare @theXml xml
declare @oldText nvarchar(50)
declare @newText nvarchar(50)
set @theXML = '<images1>http://www.mydomain.com/luxury_real_estate/florida/broward/fort_lauderdale/images/F868140_01.jpg</images1>'
set @oldText = 'images1'
set @newText = 'images'
SELECT dbo.fn_ChangeXMLText(@theXML, @oldText, @newText)
FROM theTemp


----RESULT SET-----
<images1>http://www.mydomain.com/luxury_real_estate/florida/broward/fort_lauderdale/images/F868140_01.jpg</images1>
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22732264
images1 is a node in your example and not text.  I am trapping on text.  If you want to replace nodes, change the function to be //images1.
0
 

Author Comment

by:lrbrister
ID: 22732682
So...???
SET @theXml.modify('replace value of (//images1()[.=sql:variable("@oldText")])[1] with sql:variable("@newText")')
      RETURN @theXml
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22737906
Sorry, in conference all day.

I am not certain you can do that for the node name itself.  I will have to do research to make sure.

Regards,
Kevin
0
 

Author Closing Comment

by:lrbrister
ID: 31506690
You put me on the right track.  Will post final code later
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to check data in sql table 11 51
SQL Encryption question 2 61
Grid querry results 41 80
How can i use WITH CTE for checking exist value? 3 45
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question