February 6, 2014 at 9:19 am
Hi all,
I have a problem for which I can't find a solution, so any help would be greatly appreciated. What I basically do is creating a big xml file by nesting a lot of scalar-valued-functions into each other. It would probably possible to create that xml file without using functions by writing one big query but I'm afraid that would be extremely difficult to maintain.
An example is the following:
CREATE FUNCTION dbo.SubFunction
(
)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
RETURN
(
SELECT
'Example1' AS "ex1",
'Example2' AS "ex2"
FOR XML PATH('SubPath')
)
END
GO
;WITH XMLNAMESPACES
(
'http://www.example-namespace.com' AS ns
)
SELECT
'Main' AS "ns:Main",
dbo.SubFunction()
FOR XML PATH ('ns:doc')
GO
DROP FUNCTION dbo.SubFunction
This code creates an xml-file that looks as follows:
<ns:doc xmlns:ns="http://www.example-namespace.com">
<ns:Main>Main</ns:Main>
<SubPath>
<ex1>Example1</ex1>
<ex2>Example2</ex2>
</SubPath>
</ns:doc>
This xml-file is pretty close to what I actually need. My problem is that the "SubPath"-node belongs to the namespace as well, thus the correct xml-file would look as follows:
<ns:doc xmlns:ns="http://www.example-namespace.com">
<ns:Main>Main</ns:Main>
<ns:SubPath>
<ex1>Example1</ex1>
<ex2>Example2</ex2>
</ns:SubPath>
</ns:doc>
I tried to implement that by adding the "ns"-Namespace to the function creating the subpath:
CREATE FUNCTION dbo.SubFunction
(
)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
RETURN
(
SELECT
'Example1' AS "ex1",
'Example2' AS "ex2"
FOR XML PATH('ns:SubPath')
)
END
GO
Unfortunately this is not allowed, I get the following error-message:
XML name space prefix 'ns' declaration is missing for FOR XML row, Name 'ns:SubPath'
My first idea was to add the xmlnamespace directly into the function, but that doesn't work either:
CREATE FUNCTION dbo.SubFunction
(
)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
RETURN
(
;WITH XMLNAMESPACES
(
'http://www.example-namespace.com' AS ns
)
SELECT
'Example1' AS "ex1",
'Example2' AS "ex2"
FOR XML PATH('ns:SubPath')
)
END
GO
Incorrect syntax near ';'.
Even if it worked, I assume that the namespace would be repeated in the ns:SubPath-element which would not be a good solution either.
Does anybody have an idea on how to solve this problem?
February 7, 2014 at 8:45 am
CREATE FUNCTION dbo.SubFunction
(
)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
DECLARE @xml XML
;WITH XMLNAMESPACES
(
'http://www.example-namespace.com' AS ns
)
SELECT @xml=(
SELECT
'Example1' AS "ex1",
'Example2' AS "ex2"
FOR XML PATH('ns:SubPath')
)
RETURN @xml
END
GO
;WITH XMLNAMESPACES
(
'http://www.example-namespace.com' AS ns
)
SELECT
'Main' AS "ns:Main",
dbo.SubFunction()
FOR XML PATH ('ns:doc')
GO
DROP FUNCTION dbo.SubFunction
February 10, 2014 at 10:41 am
Thank you very much! That works.
March 27, 2015 at 2:08 am
For fun, here is a slightly different way of constructing the desired XML output
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
;WITH XMLNAMESPACES
(
'http://www.example-namespace.com' AS ns
)
,SAMPLE_DATA AS
(
SELECT * FROM
(VALUES ('Example1','Example2')
) AS X(ex1,ex2)
)
SELECT
'Main' AS 'ns:Main'
,SD.ex1 AS 'ns:SubPath/ex1'
,SD.ex2 AS 'ns:SubPath/ex2'
FROM SAMPLE_DATA SD
FOR XML PATH(''), ROOT('ns:doc');
Output
<ns:doc xmlns:ns="http://www.example-namespace.com">
<ns:Main>Main</ns:Main>
<ns:SubPath>
<ex1>Example1</ex1>
<ex2>Example2</ex2>
</ns:SubPath>
</ns:doc>
March 27, 2015 at 2:37 am
Not to take anything away from what joe put together for you I bet if you put Eirikur's solution into an inline table valued function it would perform much better than any scalar UDF that produces the same XML.
Note this article:
How to Make Scalar UDFs Run Faster (SQL Spackle)[/url]
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply