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?

Could you post sample data in sqlfiddle.com (more info: Tips for asking a good Structured Query Language (SQL) question — har07
– har07, Commented Jan 26, 2016 at 4:41
@Shnugo, you're absolutely right and I do apologise for the inattentiveness. And thank you for the vote :) — Danzig
– Danzig, Commented Feb 15, 2016 at 0:39

Gottfried Lesigang · Accepted Answer · 2016-01-26 09:39:23Z

3

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

answered Jan 26, 2016 at 9:39

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Danzig Over a year ago

Thanks Shnugo - your suggestion got me to my solution. Here's what worked for me: ` ;WITH XMLNAMESPACES('james.newtonking.com/projects/json' AS json) SELECT @XML = ( SELECT 'true' AS '@json:Array', 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 james.newtonking.com namespace seemed to be required.

Collectives™ on Stack Overflow

Add attribute with colon to xml node with FOR XML PATH

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related