CODEWITHSUNDEEP
sql-serverxmlt-sqlsql-server-2005
freefaller
freefaller

Reputation: 19953

Combine and modify XML in TSQL

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

Upvotes: 3

Views: 190

Answers (1)

Matt
Matt

Reputation: 1431

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>

Upvotes: 2

Related Questions