Use xpath replace on xml value in TSQL

Posted on 2010-01-04
Medium Priority
Last Modified: 2012-05-08
I can not work out how to use the xpath replace function on values. What I want to achieve is this (not compiling):

ref.value('replace((SomeDecimalAsText)[1]',''.'',''''), 'decimal(10,2)') AS [Num1],
FROM @XMLParameter.nodes('//root/element') R(ref)

The error message I receive is:
XQuery [value()]: The '{http://www.w3.org/2004/07/xpath-functions}:replace()'-function does not exists

It does, I just think I handle it wrong or it really does not exist in the value context. Sure I could retrieve the values as varchar and do a TSQL replace, then convert it to decimal, but that would be only second choice.
Question by:Stephan_Schrandt
  • 2
  • 2
LVL 15

Expert Comment

ID: 26172351
You can use only XPath language inside your value function.

You must convert your value SomeDecimalAsText and then use the replace function outside the value function, something like this:

SELECT replace(ref.value('SomeDecimalAsText[1]', 'VARCHAR(10)'), '.', '')
FROM @XMLParameter.nodes('/root/element') R(ref)

Author Comment

ID: 26172424
Yes, but replace is also a xpath function. See http://www.w3.org/TR/2007/REC-xpath-functions-20070123/
LVL 15

Accepted Solution

jorge_toriz earned 2000 total points
ID: 26175405
Remember that sql server doesn't implement all the xpath functions, when i needed to use the replace function I used like my example.

Author Comment

ID: 26175476
The solution is that XQuery in SQL2005 is missing functions, the supported ones are not those who are defined in the official XPath documentation.


So I will fall back to the replace function of TSQL and convert twice. Thank you MS.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

592 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