CODEWITHSUNDEEP
c#sqlsql-server-2008t-sql
Tun
Tun

Reputation: 912

How to add attributes in xml from sql script

Having difficulties on adding attributes in middle of a xml tags. The following sql script return a xml format but without attributes on

Expected result:

    <retail:customerPreference xmlns:core="http://www.btor.com/core" xmlns:hta="http://docs.oasis-open.org/ns/bpel4people/ws-humantask/api/200803" xmlns:htd="http://docs.oasis-open.org/ns/bpel4people/ws-humantask/200803" xmlns:htt="http://docs.oasis-open.org/ns/bpel4people/ws-humantask/types/200803" xmlns:ns10="http://www.btor.com/addressLookup/service" xmlns:ns11="http://www.btor.com/retail/storedRetailTransaction/service" xmlns:ns12="http://www.btor.com/retail/restaurantTableStatus/service" xmlns:ns13="http://www.btor.com/crm/customerLoyalty/service" xmlns:ns7="http://www.btor.com/crm" xmlns:ns9="http://www.btor.com/retail/storedRestaurantSaleTransaction/service" xmlns:retail="http://www.btor.com/retail" xmlns:sref="http://docs.oasis-open.org/wsbpel/2.0/serviceref" xmlns:tools="http://www.enactor.com/tools" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <retail:created>2015-03-03T13:42:55Z</retail:created>
    <retail:optionPathId>Email:Email Optin</retail:optionPathId>
    <retail:optionSetId type="customerPreferenceOptionSet" optionSetId="ContactPermissions">
        <retail:groupId groupHierarchyId="All" groupTypeId="region">All</retail:groupId>
    </retail:optionSetId>
    <retail:customerId>0001505741504</retail:customerId>
    <retail:lastUpdated>2015-03-03T13:43:03Z</retail:lastUpdated>
    <retail:preferenceId>44e6-:ebcdd8fdb41:fbc61655-:24b6c309b24b4b57</retail:preferenceId>
    <retail:value id="Email:Email Optin">true</retail:value>
</retail:customerPreference>

SQL script

        ;
        WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi, 'http://www.btor.com/retail' AS retail, 'http://www.btor.com/core' AS core)
       select top 10(select 
                'Email:Email Optin' as [retail:optionPathId],
                 (select 'All' as [retail:groupId]
                 --,'customerPreferenceOptionSet' as  '@type'
                 --,'ContactPermissions' as '@optionSetId'
                 For xml path(''), type, ROOT('retail:optionSetId'))
                 ,'123' as [retail:customerId]
                ,'true' as [retail:value]
                ,'1' as [retail:preferenceId]
                For xml path(''), type, ROOT('retail:customerPreference'))
                --, 
          --Opt_Out_Mail, 
          --Opt_Out_SMS, 
          --Opt_Out_Telephone, 
          --contact_id
    from [dbo].[Permissions] 
      For XML path(''), ELEMENTS

I don't know how to add attribute in one particular tag.

which is on this:

<retail:optionSetId type="customerPreferenceOptionSet" optionSetId="ContactPermissions">

Upvotes: 1

Views: 62

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can specify the node hierarchy in the column alias part.

with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS xsi, 
                   'http://www.btor.com/retail' AS retail, 
                   'http://www.btor.com/core' AS core)
select 'Email:Email Optin' as [retail:optionPathId],
       'customerPreferenceOptionSet' as  [retail:optionSetId/@type],
       'ContactPermissions' as [retail:optionSetId/@optionSetId],
       'All' as [retail:optionSetId/retail:groupId],
       '123' as [retail:customerId],
       'true' as [retail:value],
       '1' as [retail:preferenceId]
for xml path('retail:customerPreference'), type

Result:

<retail:customerPreference xmlns:core="http://www.btor.com/core" xmlns:retail="http://www.btor.com/retail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <retail:optionPathId>Email:Email Optin</retail:optionPathId>
  <retail:optionSetId type="customerPreferenceOptionSet" optionSetId="ContactPermissions">
    <retail:groupId>All</retail:groupId>
  </retail:optionSetId>
  <retail:customerId>123</retail:customerId>
  <retail:value>true</retail:value>
  <retail:preferenceId>1</retail:preferenceId>
</retail:customerPreference>

Upvotes: 1

Related Questions