Post reply

Root note attributes FOR XML

  • April 23, 2010 at 2:54 am

    #96655

    Hi,

    I have constructed an XML message using SELECT and FOR XML...

    Using this ....

    FOR

    XML PATH('Property'), -- The element name for each row.

    ROOT('Properties') -- The root element name for this result set.

    The Root note is Properties, all works fine, except I would like to add attributes to the root node. So the XML I would like to look like this

    <Properties Version="1234">

    <Property>

    PropertyReference>1234</PropertyReference>

    </Property>

    <Properties>

    Any ideas how I can achieve this?

    Thanks

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • April 23, 2010 at 4:49 am

    #1156707

    Hi,

    I've solved this.... i used this as an example

    with xmlnamespaces('http://ww.w3.org/TR/html4/' as h)

    select getDate() as '@timestamp',

    ( select name as '@name', xtype as 'data/@xtype'

    from sysobjects

    for xml path('object'),type

    )

    for xml path('root')

    Basically I wrapped the existing query with another query that generated the root node

    Thanks for looking

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • May 10, 2010 at 9:24 am

    #1165062

    Thanks for updating! Good tip!

  • May 11, 2010 at 8:09 am

    #1165566

    Hi

    I know you have solved this already, but wanted to share something I had also used. I used this because I had problems with adding the Select before the query to generate the XML giving the correct data but the tags showed up as text representation.

    FOR XML Path('Object'), ROOT('RootNode')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply