• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1140
  • Last Modified:

Select REPLACE in XML

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
lrbrister
Asked:
lrbrister
  • 4
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
lrbristerAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
lrbristerAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
lrbristerAuthor Commented:
So...???
SET @theXml.modify('replace value of (//images1()[.=sql:variable("@oldText")])[1] with sql:variable("@newText")')
      RETURN @theXml
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
lrbristerAuthor Commented:
You put me on the right track.  Will post final code later
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now