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.
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.
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.
SQLXMLMicrosoft SQL Server
Last Comment
ste5an
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
In addition to @lcohan:
XQuery and XPath in T-SQL are only a small subset of what is defined by W3C.
XQuery and XPath in T-SQL are only a small subset of what is defined by W3C.
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?
<<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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
2. There is an article about XML Query here:
https://www.sqlshack.com/filtering-xml-columns-using-xquery-in-sql-server/