Home Forums Programming XML adding an xml root attribute RE: adding an xml root attribute

  • I'm not sure whether you can do it with the new 2005 XML PATH/ROOT features but it's certainly possible using the more complicated (but more powerful) XML EXPLICIT as follows:

    SELECT 1 AS Tag,

    NULL AS Parent,

    GETDATE() AS 'root!1!timestamp',

    NULL AS 'object!2!name',

    NULL AS 'data!3!field1'

    UNION ALL

    SELECT 2,

    1,

    NULL,

    name,

    NULL

    FROM sys.objects

    UNION ALL

    SELECT 3,

    2,

    NULL,

    name,

    type

    FROM sys.objects

    ORDER BY 'object!2!name', 'data!3!field1'

    FOR XML EXPLICIT