SQLTeam.com | Weblogs | Forums

Move one XML node to another


#1

I wish to move the data in one xml node to another, specifically the value of Text4Size50 to Text11Size50

The code below is my attempt to copy the data. Any help is appreciated.

UPDATE
dbo.tblDataPermit
Set
XmlDataField.value('(//[local-name()="Text11Size50"])[1]', 'nvarchar(max)') = XmlDataField.value('(//[local-name()="Text4Size50"])[1]', 'nvarchar(max)')
Where Type = '72'


#2

can you please provide a real sample xml data?


#3

(XmlDataField xmlns="http://MgmsSchema/Business_Information_Detail_Entry" ApplicationNumber="00000940"><Text1Size50>ROSEANN GRILLO</Text1Size50><Text5Size10 /><Text10Size50 /><Text2Size50 /><Number1>0</Number1><Text1Size10 /><Text4Size10>C</Text4Size10><Memo2 /><Text3Size50>Roseann Grillo</Text3Size50><Text2Size10>66</Text2Size10><Text12Size50 /><Text13Size50 /><Text14Size50 /><Text15Size50 /><Text3Size10>80pb</Text3Size10><YesNo5>False</YesNo5><ExtraYesNo5>False</ExtraYesNo5><YesNo4>False</YesNo4><YesNo6>False</YesNo6><Text4Size50>B</Text4Size50><ExtraYesNo1>False</ExtraYesNo1><YesNo1>False</YesNo1><ExtraYesNo2>False</ExtraYesNo2><ExtraYesNo3>False</ExtraYesNo3><YesNo3>False</YesNo3><YesNo7>False</YesNo7><YesNo8>False</YesNo8><YesNo9>False</YesNo9><YesNo10>False</YesNo10><Date1>12/30/1899 12:00:00 AM</Date1><Date2>12/30/1899 12:00:00 AM</Date2><YesNo2>False</YesNo2><Memo1>{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}} \viewkind4\uc1\pard\f0\fs17 \par } </Memo1><Text5Size50 /><Number2>0</Number2><ExtraMemo1 /><ExtraMemo2 /><ExtraMemo3 /><ExtraMemo4 /><Extramemo5 /><ExtraMemo6 /><ExtraMemo7 /><ExtraMemo8 /><ExtraMemo9 /><ExtraMemo10 /><ExtraText1>B</ExtraText1><ExtraText6 /><Text6Size50 /><ExtraText2 /><ExtraText7 /><Text7Size50 /><ExtraText3 /><ExtraText8 /><Text8Size50 /><ExtraText4 /><ExtraText9 /><Text9Size50 /><ExtraText5 /><ExtraYesNo4>False</ExtraYesNo4><ExtraText10 /><ExtraLookup1>0</ExtraLookup1><ExtraLookup2>0</ExtraLookup2><ExtraLookup3>0</ExtraLookup3><ExtraLookup4>0</ExtraLookup4><ExtraLookup5>0</ExtraLookup5><ExtraCurrency1>0.0000</ExtraCurrency1><ExtraCurrency2>0.0000</ExtraCurrency2><ExtraCurrency3>0.0000</ExtraCurrency3><ExtraCurrency4>0.0000</ExtraCurrency4><ExtraCurrency5>0.0000</ExtraCurrency5><ExtraNumber1>0</ExtraNumber1><ExtraNumber2>0</ExtraNumber2><ExtraNumber3>0</ExtraNumber3><ExtraNumber4>0</ExtraNumber4><ExtraNumber5>0</ExtraNumber5><ExtraDate1>12/30/1899 12:00:00 AM</ExtraDate1><ExtraDate2>12/30/1899 12:00:00 AM</ExtraDate2><ExtraDate3>12/30/1899 12:00:00 AM</ExtraDate3><ExtraDate4>12/30/1899 12:00:00 AM</ExtraDate4><ExtraDate5>12/30/1899 12:00:00 AM</ExtraDate5></XmlDataField)


#4

First pass, needs some solid testing. just using a simple replace :fearful:

declare @xml  varchar(max) = '<XmlDataField xmlns="http://MgmsSchema/Business_Information_Detail_Entry" ApplicationNumber="00000940"><Text1Size50>ROSEANN GRILLO</Text1Size50><Text5Size10 /><Text10Size50 /><Text2Size50 /><Number1>0</Number1><Text1Size10 /><Text4Size10>C</Text4Size10><Memo2 /><Text3Size50>Roseann Grillo</Text3Size50><Text2Size10>66</Text2Size10><Text12Size50 /><Text13Size50 /><Text14Size50 /><Text15Size50 /><Text3Size10>80pb</Text3Size10><YesNo5>False</YesNo5><ExtraYesNo5>False</ExtraYesNo5><YesNo4>False</YesNo4><YesNo6>False</YesNo6><Text4Size50>B</Text4Size50><ExtraYesNo1>False</ExtraYesNo1><YesNo1>False</YesNo1><ExtraYesNo2>False</ExtraYesNo2><ExtraYesNo3>False</ExtraYesNo3><YesNo3>False</YesNo3><YesNo7>False</YesNo7><YesNo8>False</YesNo8><YesNo9>False</YesNo9><YesNo10>False</YesNo10><Date1>12/30/1899 12:00:00 AM</Date1><Date2>12/30/1899 12:00:00 AM</Date2><YesNo2>False</YesNo2><Memo1>{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}} \viewkind4\uc1\pard\f0\fs17 \par } </Memo1><Text5Size50 /><Number2>0</Number2><ExtraMemo1 /><ExtraMemo2 /><ExtraMemo3 /><ExtraMemo4 /><Extramemo5 /><ExtraMemo6 /><ExtraMemo7 /><ExtraMemo8 /><ExtraMemo9 /><ExtraMemo10 /><ExtraText1>B</ExtraText1><ExtraText6 /><Text6Size50 /><ExtraText2 /><ExtraText7 /><Text7Size50 /><ExtraText3 /><ExtraText8 /><Text8Size50 /><ExtraText4 /><ExtraText9 /><Text9Size50 /><ExtraText5 /><ExtraYesNo4>False</ExtraYesNo4><ExtraText10 /><ExtraLookup1>0</ExtraLookup1><ExtraLookup2>0</ExtraLookup2><ExtraLookup3>0</ExtraLookup3><ExtraLookup4>0</ExtraLookup4><ExtraLookup5>0</ExtraLookup5><ExtraCurrency1>0.0000</ExtraCurrency1><ExtraCurrency2>0.0000</ExtraCurrency2><ExtraCurrency3>0.0000</ExtraCurrency3><ExtraCurrency4>0.0000</ExtraCurrency4><ExtraCurrency5>0.0000</ExtraCurrency5><ExtraNumber1>0</ExtraNumber1><ExtraNumber2>0</ExtraNumber2><ExtraNumber3>0</ExtraNumber3><ExtraNumber4>0</ExtraNumber4><ExtraNumber5>0</ExtraNumber5><ExtraDate1>12/30/1899 12:00:00 AM</ExtraDate1><ExtraDate2>12/30/1899 12:00:00 AM</ExtraDate2><ExtraDate3>12/30/1899 12:00:00 AM</ExtraDate3><ExtraDate4>12/30/1899 12:00:00 AM</ExtraDate4><ExtraDate5>12/30/1899 12:00:00 AM</ExtraDate5></XmlDataField>'
create table #tblDataPermit(Type varchar(50), XmlDataFieldCurrent xml, XmlDataFieldAfterChange xml null)

insert into #tblDataPermit(Type, XmlDataFieldCurrent)
select '72', @xml union
select '73', @xml union
select '74', @xml

update tgt
   set tgt.XmlDataFieldAfterChange = 
replace(cast(tgt.XmlDataFieldCurrent as varchar(max)), 'Text4Size50','Text11Size50')
from #tblDataPermit tgt
where Type = '72'

select * from #tblDataPermit

#5

I need to repost this


#6

oh you dont want to change xml tag but xml value? sorry I think I misunderstood what you were asking.


#7

Yes, the node value:

Text4Size50 = Grapes
Text11Size50 = NULL

Becomes:
Text4Size50 = NULL
Text11Size50 = Grapes

Or the values can be copied if moving is too difficult.


#8

it is going to involve xQuery type of stuff which is a bit difficult for me to handle.
but something like the following should do it, keep playing with it. for some reason it is not updating it for me.

update #tblDataPermit
   SET XmlDataFieldAfterChange.modify('replace value of (/XmlDataField/Text4Size50/text())[1] with ("xyz")')
where Type = '72'

#9

Thank you. I will let you know.