Problem with creating XML file by nesting functions

  • 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?

  • 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

  • Thank you very much! That works.

  • 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>

  • 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]

    "I cant 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."

    -- 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