XML : Add attribute with colon to xml node with FOR XML PATH

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?

No comments:

Post a Comment