Combine and modify XML in TSQL

Question

Using SQL Server 2005, is it possible to combine XML and add an attribute at same time?

Unfortunately, due to project restrictions, I need a SQL Server 2005 solution.

Consider the following, where I need to combine XML from multiple rows within a new <root> element...

; WITH [TestTable] AS (
    SELECT 7 AS [PkId], CAST('<data><id>11</id><id>12</id></data>' AS XML) AS [Data]
    UNION ALL
    SELECT 12, CAST('<data><id>22</id></data>' AS XML)
    UNION ALL
    SELECT 43, CAST('<data><id>33</id></data>' AS XML)
)
SELECT (
    SELECT XMLDATA as [*]
    FROM (
        SELECT [Data] AS [*]
        FROM [TestTable]
        FOR XML PATH(''), TYPE
    ) AS DATA(XMLDATA)
    FOR XML PATH('root')
)

This produces the desired output of...

<root>
  <data><id>11</id><id>12</id></data>
  <data><id>22</id></data>
  <data><id>33</id></data>
</root>

But what I need to do, if possible, is add an attribute to the existing data element in each of the rows with the PkId value. The desired output would then look like this...

<root>
  <data pkid="7"><id>11</id><id>12</id></data>
  <data pkid="12"><id>22</id></data>
  <data pkid="43"><id>33</id></data>
</root>

My gut feeling is that this is going to be impossible without the use of a cursor, but if anybody knows a way of doing it I'd love to hear it.

At the request of @MattA, here is an example of some random data in the table...

[PkId]  [UserId]  [SubmittedDate]      [Data]
1       1         2015-03-24 12:34:56  '<data><id>1</id><id>2</id></data>'
2       1         2015-03-23 09:15:52  '<data><id>3</id></data>'
3       2         2015-03-22 16:01:23  '<data><id>4</id><id>5</id></data>'
4       1         2015-03-21 13:45:34  '<data><id>6</id></data>'

Please note, that to make the question easier, I stated that I needed the PkId column as the attribute to the data. This is not actually the case - instead I need the [SubmittedDate] column to be used. I apologise if this caused confusion.

Using UserId=1 as a filter, the XML I would like from the above would be...

<root>
  <data submitteddate="2015-03-24T12:34:56"><id>1</id><id>2</id></data>
  <data submitteddate="2015-03-23T09:15:52"><id>3</id></data>
  <data submitteddate="2015-03-21T13:45:34"><id>6</id></data>
</root>

The date would be formatted using the 126 date format available from CONVERT

XML is fully CRUD compliant in SQL Server. I'll work up an example in my free time today unless someone else gets to it first. — Matt, Mar 24 '15 at 15:22
See if this helps you: http://stackoverflow.com/questions/13234175/use-of-xml-modify-to-insert-parameters-into-specific-element-of-an-xml-column — Dbloch, Mar 24 '15 at 15:28

Matt · Answer 1 · 2015-03-24T16:02:22.360

2

Here's the quick answer for you. XML does support "modify", but shredding on a small data set like this works quite well too.

Code

--The existing XML
DECLARE @XML XML = '<root>
  <data><id>11</id></data>
  <data><id>22</id></data>
  <data><id>33</id></data>
</root>'

--XML Shredded Back to a table
;WITH
ShreddedXML AS (
    SELECT
        ID = FieldAlias.value('(id)[1]','int')
    FROM
        @XML.nodes('/root/data') AS TableAlias(FieldAlias)
), ArbitraryPKGenerator AS (
    SELECT  CURRENT_TIMESTAMP AS PKid,
            ID
    FROM    ShreddedXML
)
SELECT  A.PKId AS "@PKid",
        A.ID AS "id"
FROM    ArbitraryPKGenerator AS A
FOR     XML PATH('data'), ROOT('root')

And the XML

<root>
  <data PKid="2015-03-24T09:44:55.770">
    <id>11</id>
  </data>
  <data PKid="2015-03-24T09:44:55.770">
    <id>22</id>
  </data>
  <data PKid="2015-03-24T09:44:55.770">
    <id>33</id>
  </data>
</root>

edited Mar 24 '15 at 16:02

answered Mar 24 '15 at 15:39

Matt

1,441
1
15
29

Thanks for your answer Matt... the `pkid` was a simple example, but not actually what I need. The actual data that will be added as an attribute will be from a `datetime` column in the table... so I'm not sure this method is going to work for my specific case – freefaller Mar 24 '15 at 15:42
Have a look at the update. I put CURRENT_TIMESTAMP to showcase a datetime being the @PKid column's source. What you'll be doing in that step is joining to your source data and pulling the column you want from the source table. I think CURRENT_TIMESTAMP is 2005 compatible, but GET_DATE() will work too. – Matt Mar 24 '15 at 15:49
Getting closer Matt - and I really do appreciate your effort, but the value for the attribute is taken from a **column** in the table that also contains the XML fields. So I need the `` for the XML in the row to have an attribute of `pkid` where the value of that attribute is the datetime value from the same row. – freefaller Mar 24 '15 at 16:09
Please add a sample of the table where you expect to get your datetime column from. Please also include the value as it correlates to the id field. I can whip up a quick example but I'd rather just nail exactly what you're asking for. – Matt Mar 24 '15 at 16:17
See updated question - I hope that makes it clearer, and I apologise for any confusion. I was trying to make the question easier by just stating the `pkid` field, with the intention of changing to the `submitteddate` field after it was working – freefaller Mar 24 '15 at 16:29
No problem. Give me about 5 min. We're going to go a different direction since these are all in the same table. – Matt Mar 24 '15 at 16:30
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/73683/discussion-between-matt-a-and-freefaller). – Matt Mar 24 '15 at 17:11
Really sorry Matt, I've reached the end of my working day, and I need to leave the office! Thanks for you help though – freefaller Mar 24 '15 at 17:16

Combine and modify XML in TSQL

1 Answers1