Use xpath replace on xml value in TSQL

Posted on 2010-01-04
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 '{}: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
    LVL 15

    Expert Comment

    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)
    LVL 9

    Author Comment

    Yes, but replace is also a xpath function. See
    LVL 15

    Accepted Solution

    Remember that sql server doesn't implement all the xpath functions, when i needed to use the replace function I used like my example.
    LVL 9

    Author Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    This video discusses moving either the default database or any database to a new volume.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now