Add attribute with colon to xml node with FOR XML PATH

Question

I am trying to modify a stored proc to contain the following:

SET @XML = (    
    SELECT Category.Title,
        (       
            SELECT 'true' AS [@json:Array], Book.Name, Book.Value
            FROM @Book Book
            WHERE Category.CategoryID = Book.CategoryID
            FOR XML PATH('Values'), ROOT('Book'), TYPE
        ) 
    FROM @Category Category
    FOR XML PATH('Category'), ROOT('Response')
) 

The "SELECT 'true' AS [@json:Array]" is there to force the xml to add "json:Array='true' to the values node so that even if there's only one child element it will be contained in an array. But, the @json:Array throws an error: "XML name space prefix 'json' declaration is missing for FOR XML column name '@json:Array'."

I've looked at links like this but they all seem to deal with adding attributes that don't include a colon. I've also tried adding "WITH NAMESPACES..." but couldn't get the syntax right.

Can someone tell me how to modify the SQL to have this work?

Answer 1

If you do it like this:

DECLARE @XML XML;
WITH XMLNAMESPACES('xmlns:json' AS json)
SELECT @XML=
(
    SELECT 'YourTitle',
        (       
            SELECT 'true' AS [@json:Array], 'BookName', 'BookValue'
            FOR XML PATH('Values'), ROOT('Book'), TYPE
        ) 
    FOR XML PATH('Category'), ROOT('Response')
) 
SELECT @xml

... you'll get the attribut. But the price is a repeated namespace in all of your root nodes (in nested too).

This might be a trick, but you'll have to declare your namespace in the top element:

DECLARE @XML XML;
SELECT @XML=
(
    REPLACE(REPLACE(
    (
    SELECT 'YourTitle',
        (       
            SELECT 'true' AS [@jsonArray], 'BookName', 'BookValue'
            FOR XML PATH('Values'), ROOT('Book'), TYPE
        ) 
    FOR XML PATH('Category'), ROOT('Response')
    ),'jsonArray','json:Array'),'<Response','<Response xmlns:json="urnJson"')
); 

SELECT @xml