1

chtml columns of ntext:

<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>

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:  "<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 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 */

2
  • 1) You open Physicians but you close Phys and 2) Are there really two separate root-elements? Commented Oct 6, 2015 at 19:09
  • Just one root element. Commented Oct 6, 2015 at 19:19

1 Answer 1

3

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=
'<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 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='<a title="' + @langTitle + '" href="' +  @langLink + '">' + @langTitle + '</a>';

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;
Sign up to request clarification or add additional context in comments.

14 Comments

the 'F' is taken from another table. But I see what you are doing. Any idea about the LOCATION section? Thanks. It worked after a small tweak. Can you please help with updating the link for the LOCATION?
I +1 the answer. Now testing location. If it works, I will surely accept it :) Thanks.
@SiKni8, Don't know if I understand this... You can modifiy the content or delete the element/attribut. If this doesn't help please give an example.
@SiKni8, Everything solved now? Good luck and happy coding!
@SiKni8, this should be a case for "replace value of", read this: msdn.microsoft.com/en-us/library/…
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.