How to replace XML variable through SQL

Question

chtml columns of ntext:


    
          Doctor1
          M
          English
    


    
         Doctor2
         F
         French

Staging table (tableStage):

Name        Gender      Language 1
Doctor1     F           French
Doctor2     M           Spanish

Stored procedure which updates the chtml column in a table from the value from the 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:  "Doctor1M" instead of Doctor1MFrench */

/* 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 resolve the two issues I am having:

/* 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" */
/* THE ABOVE CHANGE chtml to: "Doctor1M" instead of Doctor1MFrench */

Gottfried Lesigang · Accepted Answer

actually I do not know what (and why) you do this. This seems to be quite complicated...

You can solve the error you get as follows:

Just paste this into an empty query window and transfer the idea into your code. The point is the second DECLARE @genderVal which is commented. Change the comments to the other DECLARE and you can reproduce it. If this is NULL you'll get the error you described. That means: check the value of your variable. It seems to be NULL.

Be aware, that I corrected the wrong closing tags "Phys" and I removed the inner "root"-tags...

declare @xml XML=
'
  
    Doctor1
    M
    
      English
    
  
  
    Doctor2
    F
    
      French
    
  
';
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 have to bother, whether a language exists or not. You just delete it in any case

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

Now you build your inner xml for "a":

DECLARE @langTitle varchar(100)='French';
DECLARE @langLink varchar(100)='/default.aspx?id=56';
DECLARE @newLang XML='' + @langTitle + '';

This inner 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;

How to replace XML variable through SQL

Answers (1)

Related Questions