Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with creating XML file by nesting functions Expand / Collapse
Author
Message
Posted Thursday, February 6, 2014 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 10:05 AM
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?
Post #1538724
Posted Friday, February 7, 2014 8:45 AM This worked for the OP Answer marked as solution
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:18 PM
Points: 44, 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
Post #1539234
Posted Monday, February 10, 2014 10:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 10:05 AM
Points: 5, Visits: 54
Thank you very much! That works.
Post #1539876
Posted Friday, March 27, 2015 2:08 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 3,860, Visits: 9,964
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>

Post #1672011
Posted Friday, March 27, 2015 2:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:21 AM
Points: 1,051, Visits: 4,007
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1672021
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse