CODEWITHSUNDEEP
sqlsql-server
user2044161
user2044161

Reputation: 39

Read from XML field and write to another XML field sql server

I have two XML fields in a table say A and B. A has data like :

                <periods>
                <historicalperiod>2</historicalperiod>
                <historicalperiod>4</historicalperiod>
                <historicalperiod>6</historicalperiod>
                <historicalperiod>8</historicalperiod>
                </periods>

The above XML can have variable number of nodes.

I have to copy this data to field B in following format :

                <periods>
                <historicalperiod1>2</historicalperiod1>
                <historicalperiod2>4</historicalperiod2>
                <historicalperiod3>6</historicalperiod3>
                <historicalperiod4>8</historicalperiod4>
                 </periods>

I tried this by using temp table

                create table temp 
                (period int )

                ;with cte as (
                select
                 T.C.value('.', 'nvarchar(max)') as period
                 from  BatchQuotaSettings 
                 CROSS APPLY HistoryPeriods.nodes('/periods/historicalperiod') as T(C)     
                    )
                 insert into temp (period)
                 select c.period
                  from cte c

Is there better way to do this?

Upvotes: 1

Views: 53

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Shred the XML and use row_number to enumerate the shredded nodes. Build your new XML nodes as strings, cast to XML and combine the nodes using for xml path.

update T
set B = (
        select cast('<historicalperiod'+cast(S.R as varchar(10))+'>'+S.V+'</historicalperiod'+cast(S.R as varchar(10))+'>' as xml)
        from (
             select P.X.value('text()[1]', 'varchar(10)') as V,
                    row_number() over(order by P.X) as R
             from T.A.nodes('/periods/historicalperiod') as P(X)
             ) as S
        for xml path(''), root('periods'), type
        )
from BatchQuotaSettings as T

SQL Fiddle

Upvotes: 1

Related Questions