CODEWITHSUNDEEP
sql-serverxml
Arif YILMAZ
Arif YILMAZ

Reputation: 5866

adding an attribute to xml root in sql server

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')

Upvotes: 1

Views: 268

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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')

Upvotes: 2

Related Questions