How to replace XML variable through SQL

Question

chtml columns:

 <root> <Phys> <name>Doctor1</name> <gender>M</gender> <langF1><a title="English" href="/default.aspx?id=23">English</a></langF1> </Phys> </root> <root> <Phys> <name>Doctor2</name> <gender>F</gender> <langF1><a title="French" href="/default.aspx?id=56">French</a></langF1> </Phys> </root> 

Lookup table ( tableStage ):

 Name Gender Language 1 Doctor1 F French Doctor2 M Spanish 

A chtml procedure that updates the chtml column in a table from a value from a staging table:

 ALTER PROCEDURE [dbo].[Pro] ( @ProviderName varchar(200), -- "Doctor1" for example @genderVal varchar(10), @langID varchar(10), @langTitle varchar(60), @langUrl varchar(255), @anchorLang varchar(255), @langLinkXML xml, ) AS BEGIN DECLARE @xml xml Select @xml = cast([chtml] as xml) --cast the chtml column which is of ntext type From [myDb].[dbo].[myTable] --to xml so we can update the nodes Where [content_title] = @ProviderName --Update gender val (if exists, update, otherwise insert) Select @genderVal = t1.[Gender] From [myDb].[dbo].[tableStage] t1 --replace Value2 and table to the table which is updated through SSIS Where t1.Name = @ProviderName PRINT @genderVal If @xml IS NOT NULL BEGIN Set @xml.modify('replace value of (/root/Physicians/gender/text())[1] with sql:variable("@genderVal")'); END Else BEGIN PRINT 'NOT WORK' END /* ERROR for above is: "XQuery: Replacing the value of a node with an empty sequence is allowed only if '()' is used as the new value expression. The new value expression evaluated to an empty sequence but it is not '()'." instead of changing the gender to "F" */ --Update language one field (if exist, delete and add, otherwise just delete) --get the language id from the temp language table which matches the language for the provider Select @langID = t1.content_id From [myDb].[dbo].[zTempLanguageTable] t1 INNER JOIN [myDb].[dbo].[tableStage] t2 On t2.[Language 1] = t1.content_title Where t2.Name = @ProviderName If @langID != '' BEGIN --get the language title from the language id Select @langTitle = t1.content_title From [myDb].[dbo].[zTempLanguageTable] t1 Where @langID = t1.content_id --set the Url (which will be part static and part dynamic with the id) Set @langUrl = '/default.aspx?id=' + @langID + ''; --delete the anchor entry Set @xml.modify('delete /root/Physicians/langF1/a'); --create the XML for the node Set @langLinkXML = ( Select @langTitle as 'a/@title', @langUrl as 'a/@href', '_blank' as 'a/@target', @langTitle as 'a' for xml path(''), type ); -- insert the new/updated anchor node Set @xml.modify('insert sql:variable("@langLinkXML") into (/root/Physicians/langF1)[1]'); END Else BEGIN --delete the anchor entry Set @xml.modify('delete /root/Physicians/langF1/a'); END /* THE ABOVE CHANGE `chtml` to: "<root><Physicians><name>Doctor1</name><gender>M</gender></langF1></Phys></root>" instead of <root><Physicians><name>Doctor1</name><gender>M</gender><langF1><a title="French" href="/default.aspx?id=67">French</a></langF1></Phys></root> */ /* once all the node has been "temporarily" changed, update the table columns for the provider */ Update [myDb].[dbo].[myTable] Set [chtml] = cast(cast(@xml as nvarchar(max)) as ntext) //change the type to `ntext` data type for the table. Where [content_title] = @ProviderName END 

How can I solve two problems that I have:

• / * ERROR for the above: "XQuery: replacing a node value with an empty sequence is only allowed if an '()' expression is used as a new value. A new expression expression evaluated in an empty sequence but it is not a '()'." instead of changing the floor to "F" * /

• / * ABOVE chtml to: "Doctor1M" change instead of Doctor1MFrench * /

Answer 1

Actually, I don’t know what (and why) you are doing it. It seems rather complicated ...

You can solve the error that occurs:

Just paste this into an empty request window and pass the idea into your code. The point is the second DECLARE @genderVal that is commented out. Change the comments to another DECLARE and you can play it. If this value is NULL, you will receive an error message. This means: check the value of the variable. It seems to be NULL.

Remember that I fixed the invalid closing Phys tags and I removed the internal root-tags ...

 declare @xml XML= '<root> <Physicians> <name>Doctor1</name> <gender>M</gender> <langF1> <a title="English" href="/default.aspx?id=23">English</a> </langF1> </Physicians> <Physicians> <name>Doctor2</name> <gender>F</gender> <langF1> <a title="French" href="/default.aspx?id=56">French</a> </langF1> </Physicians> </root>'; select @xml; --This will search for the physician with the name "Doctor1"... Dont' know if you need this... Your example could be meant with one XML-for each physician... DECLARE @ProviderName varchar(200)='Doctor1'; --DECLARE @genderVal varchar(10)=NULL; DECLARE @genderVal varchar(10)='F'; --Update Doctor1 with new @genderVal=NULL --> The error you described SET @xml.modify('replace value of (/root/Physicians[name=sql:variable("@ProviderName")]/gender/text())[1] with sql:variable("@genderVal")'); select @xml; 

EDIT: change the language

If I understand you correctly, you don’t need to worry if the language exists or not. You just delete it anyway

 SET @xml.modify('delete (/root/Physicians[name=sql:variable("@ProviderName")]/langF1/a)[1]'); select @xml; 

Now you create your internal xml for "a":

 DECLARE @langTitle varchar(100)='French'; DECLARE @langLink varchar(100)='/default.aspx?id=56'; DECLARE @newLang XML='<a title="' + @langTitle + '" href="' + @langLink + '">' + @langTitle + '</a>'; 

This internal xml can be inserted into an existing node:

 SET @xml.modify('insert sql:variable("@newLang") into (/root/Physicians[name=sql:variable("@ProviderName")]/langF1)[1]'); select @xml;