SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with creating XML file by nesting functions


Problem with creating XML file by nesting functions

Author
Message
weber-866724
weber-866724
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 54
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?
joe.eager
joe.eager
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 467

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

weber-866724
weber-866724
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 54
Thank you very much! That works.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16102 Visits: 18627
For fun, here is a slightly different way of constructing the desired XML output
Cool

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>


Alan.B
Alan.B
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5484 Visits: 7736
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)

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search