Link to home
Create AccountLog in
Avatar of Declan Basile
Declan BasileFlag for United States of America

asked on

fn:replace xml string function not recognized in SQL Server 2012

Below is a picture of the documented fn:replace xml string function posted on the web.

User generated image

yet when I run this TSQL statement:
   SELECT T.C.query('if (fn:local-name(.) = "PId") then (data(.)) else (fn:replace(fn:local-name(.), "P", ""))') As X
   FROM @XML_R.nodes('/P/*') T(C)

I get the error message:
XQuery [query()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:replace()'

1.) By taking out the replace function, I see that the fn:local-name function is recognized.  Does SQL Server actually look to the URL in the error message to run this function?
2.) Where/How is the namespace in the error message registered in SQL Server?  i.e. How does SQL Sever know about this website?
3.) Is it possible to write your own function and register it in SQL Server with a prefix for the namespace so you can call your own function in any .query method as long as you prefix the function?
4.) Why won't SQL Server recognize fn:replace even though it recognizes fn:local-name?

Note: I've tried finding answers to these questions on the web but still don't understand this well enough.

Avatar of Andrei Fomitchev
Andrei Fomitchev
Flag of United States of America image

1. Please, publish XML example that you are trying to parse.
2. There is an article about XML Query here:
https://www.sqlshack.com/filtering-xml-columns-using-xquery-in-sql-server/
SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
In addition to @lcohan:

XQuery and XPath in T-SQL are only a small subset of what is defined by W3C.
Avatar of Declan Basile

ASKER

<<XQuery and XPath in T-SQL are only a small subset>>
<<You can find a list of XQuery engines here>>

So fn:local-name is an XQuery/XPath function that is inherently recognized in SQL Server, but fn:replace is not, correct?

Is there a way to register/install an XQuery engine into SQL Server so SQL Server will recognize the fn:replace function (and other XQuery functions) when used in any .query method run by that instance of SQL Server?

Is it possible to register your own custom XQuery functions in SQL Server so that they will be globally recognized in any .query method run by that instance of SQL Server?

ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account