adding an attribute to xml root in sql server

Question

I am trying to create xml from sql server and I am almost done with my query. But, I have a problem that I couldnt figure it out. How can I add an attribute to root "ITEM", not "ITEMS"? My sql query is below...

SELECT  
            CARD_TYPE
            ,CODE
            ,NAME
            ,UNITSET_CODE
            ,AUXIL_CODE
            ,AUXIL_CODE2
            ,AUXIL_CODE3
            ,AUXIL_CODE4
            ,AUXIL_CODE5
            ,CYPHCODE
       FROM P_ITEMS
      WHERE HOSTGROUPID='7155d850-5882-43a0-b722-f6586bfffa61'
        AND TRANCODE ='ITEMS'
        AND PROCESSED ='NEW'
        FOR XML PATH('ITEM'),ROOT('ITEMS')

Gottfried Lesigang · Accepted Answer · 2016-01-16 14:52:21Z

2

Try it like this

SELECT  
        'theAttributValue' AS [@MyAttribut]
        ,CARD_TYPE
        ,CODE
        ,NAME
        ,UNITSET_CODE
        ,AUXIL_CODE
        ,AUXIL_CODE2
        ,AUXIL_CODE3
        ,AUXIL_CODE4
        ,AUXIL_CODE5
        ,CYPHCODE
   FROM P_ITEMS
  WHERE HOSTGROUPID='7155d850-5882-43a0-b722-f6586bfffa61'
    AND TRANCODE ='ITEMS'
    AND PROCESSED ='NEW'
    FOR XML PATH('ITEM'),ROOT('ITEMS')

answered Jan 16, 2016 at 14:52

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

Collectives™ on Stack Overflow

adding an attribute to xml root in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related