How do you add attributes to an existing Root XML Node using TSQL?

Question

I've constructed some XML in TSQL.

declare @requestXML xml

set @requestXML = (
select @dataXML
for xml raw ('rtEvent')

The general output for what I now have follows the pattern resembling this:

<rtEvent>
  <ctx>
    .....
  </ctx>
</rtEvent>

What I'd like to do now is add some attributes and values to the rtEvent root element node but I'm not certain how to achieve it.

I've looked at the Modify method of the XML object and have observed the insert, replace value of, and delete operations but cannot seem to figure out how to use any of them to achieve the results I'm after.

Basically, I want to be able to modify the root node to reflect something like:

<rtEvent type="customType" email="[email protected]"
  origin="eCommerce" wishedChannel="0" externalId="5515">
   <ctx>
     ...
   </ctx>
</rtEvent>

Should I be using the documented XML.Modify or is there a better method? How should it be done?

Anssssss · Accepted Answer · 2017-10-13 16:29:15Z

11

Just in case you wanted to see the modify method way of doing it:

DECLARE @requestXML XML = '<rtEvent><ctx>...</ctx></rtEvent>'
SET @requestXML.modify(
    'insert 
    (
        attribute type {"customeType"},
        attribute email {"[email protected]"},
        attribute origin {"eCommerce"},
        attribute wishedChannel {"0"},
        attribute externalId {"5515"}
    )
    into (/rtEvent)[1]')
SELECT @requestXML

it returns this:

<rtEvent type="customeType" email="[email protected]" origin="eCommerce" wishedChannel="0" externalId="5515">
  <ctx>...</ctx>
</rtEvent>

answered Oct 13, 2017 at 16:29

Anssssss

3,3411 gold badge34 silver badges44 bronze badges

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

Comments

Gottfried Lesigang · Accepted Answer · 2016-08-31 13:18:23Z

6

Better use FOR XML PATH, which allows you to specify the naming and aliases as you like them:

SELECT 'SomeContext' AS [ctx]
FOR XML PATH('rtEvent')

This will return this:

<rtEvent>
  <ctx>SomeContext</ctx>
</rtEvent>

But with the right attributes you get this:

SELECT  'customType' AS [@type]
       ,'[email protected]' AS [@email]
       ,'eCommerce' AS [@origin]
       ,0 AS [@wishedChannel]
       ,5515 AS [@externalId]
       ,'SomeContext' AS [ctx]
FOR XML PATH('rtEvent')

The result

<rtEvent type="customType" email="[email protected]" origin="eCommerce" wishedChannel="0" externalId="5515">
  <ctx>SomeContext</ctx>
</rtEvent>

answered Aug 31, 2016 at 13:18

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Collectives™ on Stack Overflow

How do you add attributes to an existing Root XML Node using TSQL?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related