I have table which is having XML column. Now this XML column have multiple tags like an array. Each of these tags have an attribute() which i want to update from a table by joining it.
Table_A is as following
Id XML
1 "<Root><Object ObjId = "1" Text = "A"><Object Id = "2" Text = "B"></Root>"
2 "<Root><Object ObjId = "1" Text = "M"><Object Id = "12" Text = "N"></Root>"
Each of this XML is as following
<Root>
<Object Id = "1" Text = "A">
<Object Id = "2" Text = "B">
<Object Id = "3" Text = "C">
<Object Id = "4" Text = "D">
<Object Id = "5" Text = "E">
</Root>
Table_B i want to join to update records in XML:
Table_A_Id ObjId Value
1 1 "Q"
1 2 "R"
2 1 "S"
2 12 "T"
I couldn't find the solution to create a dynamic path in XML.modify method. Following is the query i have started, which might explain what i am trying to do.
UPDATE TblA SET XML.modify(replace value of (/Root/Object) with Table_B.Value)
FROM TABLE_A TblA
CROSS APPLY TblA.XML.nodes('/Root/Object') AS xmlObjs(obj)
INNER JOIN Table_B TblB ON TblB.Table_A_Id = TblA.Id AND TblB.ObjId = xmlObjs.obj.value('@ObjId[1]','varchar(MAX)')
What you seem to know is: .modify()
will not allow more than one change per call. You would have to use a CURSOR
or WHILE
loop in order to update each occurance one after the other.
Therefore I'd suggest this approach:
First we create mockup tables to simulate your issue:
DECLARE @tblA TABLE(Id INT, [XML] XML)
INSERT INTO @tblA VALUES
(1,N'<Root>
<Object Id = "1" Text = "A"/>
<Object Id = "2" Text = "B"/>
<Object Id = "5" Text = "E"/>
</Root>')
,(2,N'<Root>
<Object Id = "1" Text = "F"/>
<Object Id = "2" Text = "G"/>
<Object Id = "12" Text = "J"/>
<Object Id = "13" Text = "J"/>
</Root>')
DECLARE @tblB TABLE(Table_A_Id INT,[ObjId] INT,[Value] VARCHAR(10));
INSERT INTO @tblB VALUES
(1,1 ,'Q')
,(1,2 ,'R')
,(2,1 ,'S')
,(2,12,'T');
--The query
WITH cte AS
(
SELECT tA.Id
,tA.[XML]
,(
SELECT A.obj.value('@Id','int') AS [@Id]
,COALESCE(tB.[Value],A.obj.value('@Text','varchar(max)')) AS [@Text]
FROM tA.[XML].nodes('/Root/Object') A(obj)
LEFT JOIN @tblB tB ON tB.Table_A_Id=tA.Id AND tB.[ObjId]=A.obj.value('@Id','int')
FOR XML PATH('Object'),ROOT('Root'),TYPE
) AS NewXml
FROM @tblA tA
)
UPDATE cte SET cte.[Xml]=NewXml;
--Check the result
SELECT * FROM @tblA;
The idea in short:
UPDATE
Next time please try to avoid a chameleon question... Your comment let this change into something entirely different... Next time please close one question, if it is answered as is and start a new question in case you find, that your initial question did not really cover your needs...
Try this:
DECLARE @tblA TABLE(Id INT, [XML] XML)
INSERT INTO @tblA VALUES
(1,N'<Root>
<AnotherTag val="abc"/>
<AnotherTag val="DEF"/>
<Object Id = "1" Text = "F" OtherProperty = "123" />
<Object Id = "2" Text = "G" SampleProperty = "Anything" DataProperty="Sample Data" />
<Object Id = "12" Text = "I" OtherProperty = "123"/>
<Object Id = "13" Text = "J" DataProperty = "Sample"/>
</Root>')
,(2,N'<Root>
<Object Id = "1" Text = "F"/>
<Object Id = "2" Text = "G"/>
<Object Id = "12" Text = "I"/>
<Object Id = "13" Text = "J"/>
</Root>')
DECLARE @tblB TABLE(Table_A_Id INT,[ObjId] INT,[Value] VARCHAR(10));
INSERT INTO @tblB VALUES
(1,1 ,'Q')
,(1,2 ,'R')
,(2,1 ,'S')
,(2,12,'T');
WITH cte AS
(
SELECT ta.Id
,ta.[XML]
,Combined.[CombXml].query('<Root>
{
for $elmt in /combined/embedded/Root/*
let $bVal := /combined/b_data[ObjId[1] = $elmt/@Id]/Value/text()
return
if(local-name($elmt) eq "Object") then
<Object Id="{$elmt/@Id}" Text="{if(empty($bVal)) then $elmt/@Text else $bVal}">
{$elmt/@*[local-name() != "Id" and local-name() != "Text"]}
</Object>
else
$elmt
}
</Root>') AS NewXml
FROM @tblA ta
OUTER APPLY(SELECT (SELECT [ObjId],[Value]
FROM @tblB tb
WHERE tb.Table_A_Id=ta.Id
FOR XML PATH('b_data'),TYPE) AS [*]
,ta.[XML] AS [embedded]
FOR XML PATH(''),ROOT('combined'),TYPE) Combined([CombXml])
)
UPDATE cte SET [XML] = NewXml;
SELECT * FROM @tblA;
The idea behind:
We need to get the side data into the XML in order to use XQuery-FLWOR.
The APPLY
will create a XML like this:
<combined>
<b_data>
<ObjId>1</ObjId>
<Value>Q</Value>
</b_data>
<b_data>
<ObjId>2</ObjId>
<Value>R</Value>
</b_data>
<embedded>
<Root>
<AnotherTag val="abc" />
<AnotherTag val="DEF" />
<Object Id="1" Text="F" OtherProperty="123" />
<Object Id="2" Text="G" SampleProperty="Anything" DataProperty="Sample Data" />
<Object Id="12" Text="J" OtherProperty="123" />
<Object Id="13" Text="J" DataProperty="Sample" />
</Root>
</embedded>
</combined>
Against this combined XML we can run a FLWOR query using .query()
.
<Root>
<Value>
of your side data (in the XML its <b_data>
) is assigned to $bVal
.<Object>
.Id
and Text
directly and add all other attributes without looking into them.The result for the Id=1 looks like this afterwards:
<Root>
<AnotherTag val="abc" />
<AnotherTag val="DEF" />
<Object Id="1" Text="Q" OtherProperty="123" />
<Object Id="2" Text="R" SampleProperty="Anything" DataProperty="Sample Data" />
<Object Id="12" Text="I" OtherProperty="123" />
<Object Id="13" Text="J" DataProperty="Sample" />
</Root>
You can see, that "Q" and "R" are changed where Id is 1 or 2 - according to the side data.
I must admit, that this gets complicated...
Depending on your real data and the complexity of your XML a looping approach with .modify()
might be better...