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 06, 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 07, 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: Today @ 8:24 AM
Points: 41, Visits: 422
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse