Home Forums SQL Server 2005 T-SQL (SS2K5) Using FOR XML EXPLICIT with XQuery: how to remove empty namespace? RE: Using FOR XML EXPLICIT with XQuery: how to remove empty namespace?

  • I found a workaround myself, which seems pretty dirty, but it does the trick :

    SELECT Tag = 1,

    Parent = NULL,

    'record!1!id' = Id,

    'record!1!code' = Code,

    'record!1!!XML' = CAST(XmlContent.query('//test') AS varchar(max))

    FROM @Records

    FOR XML EXPLICIT;

    By first casting the XML data field to a (n)varchar datatype, and then applying the "XML" directive instead of the "ELEMENT" directive in the FOR XML EXPLICIT construction.

    (See https://msdn.microsoft.com/en-us/library/bb510481.aspx)

    However, I hope there are better solutions, where no casting or converting is needed?

    To me, it seems strange, to have XML converted to text to be able to get proper XML in the end.