Modify(Replace) Xml for Conditions

modify(replace) XML for conditions

it's not possible to replace multiple values at once in xml in SQL Server, there're a several options:

  • use loop and update attributes one by one
  • split data into temporary table/table variable, update and then merge into one xml
  • use xquery to rebuild xml

I think correct way for you would be loop solution:

select @i = @data.value('count(//Attribute[DataType="Float" and Format="n0"]/Format)', 'int')

while @i > 0
begin
    set @data.modify('
         replace value of
             (//Attribute[DataType="Float" and Format="n0"]/Format/text())[1]
         with "f2"
    ')

    set @i = @i - 1
end

sql fiddle demo

If your xml contains namepaces, simplest way to update I found would be to declare default namespace:

;with xmlnamespaces(default 'schemas.microsoft.com/sqlserver/2004/10/semanticmodeling')
select @i = @xml.value('count(//Attribute[DataType="Float" and Format="n0"]/Format)', 'int')

while @i > 0
begin
    set @xml.modify('
         declare default element namespace "schemas.microsoft.com/sqlserver/2004/10/semanticmodeling";
         replace value of
             (//Attribute[DataType="Float" and Format="n0"]/Format/text())[1]
         with "f2"
    ')

    set @i = @i - 1
end
select @xml

How to modify an xml variable with a conditional/where clause

Here is the answer for the "..REAL LIFE case...". I modified the input XML by adding some additional elements. The XQuery was adjusted accordingly.

SQL

-- DDL and sample data population, start
DECLARE @xml xml = N'<Container>
  <city>Miami</city>
  <state>FL</state>
  <Collection>
    <foo>One</foo>
    <bar>true</bar>
    <baz>false</baz>
  </Collection>
  <Collection>
    <foo>Two</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
  <Collection>
    <foo>Three</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
</Container>';
-- DDL and sample data population, end

-- before
SELECT @xml AS [before];

-- update single element
SET @xml.modify('replace value of (/Container/Collection[upper-case((foo/text())[1]) = "TWO"]/bar/text())[1] with "false"')

-- after
SELECT @xml AS [After];

-- Method #1, via FLWOR expression
-- update all <bar> elements with the false' value
DECLARE @bar VARCHAR(10) = 'false';

SET @xml = @xml.query('<Container>
{
   for $x in /Container/*[not(local-name(.)=("Collection"))]
   return $x
}
{
    for $y in /Container/Collection
    return <Collection>
    {
        for $z in $y/*
        return 
        if (not(local-name($z) = ("bar"))) then $z
        else 
        (
            element bar {sql:variable("@bar")}
        )
    }
    </Collection>
}
</Container>');

Conditional XML Modify in SQL Server

It is incorrect syntax. You could use 2 separate statements instead:

UPDATE ITS_CSC.[Statement].[StatementSummary]
SET  [XMLData].modify('delete //invoice/account/contactinformation') 
WHERE [XMLData] IS NOT NULL;

UPDATE ITS_CSC.[Statement].[StatementSummary]
SET [SummaryXMLData].modify('delete //invoice/account/contactinformation')
WHERE [SummaryXMLData] IS NOT NULL;

Conditional replace in XML files

Here's a quick fix, but note that a more robust solution would use PowerShell's XML parsing features: see Ansgar Wiecher's helpful answer:

Note: This answer assumes that the strings of interest do not conflict with syntactical elements of the XML document, such as element names and attribute names (which happens to work for the specific strings in question), which illustrates why using a real XML parser is the better choice.

$content = @'
<doc>
<title>web site</title>
<subtitle>web-site</subtitle>
<path>c:/web site/website.xml</path>
</doc>
'@

$modifiedContent = $content -replace ''([^/])web[ -]site([^/])', '$1New Site$2'
# Replace 'web site' and 'web-site' if not preceded or followed by a '/'.
# Note: `web[ -]site` is the equivalent of `web site|web-site`

if ($modifiedContent -cne $content) { # If contents have changed, save.
  Out-File -InputObject $modifiedContent $file.FullName -Encoding utf8
}

Replacing the one child node value which is exists multiple places with in XML

When using XML modify() the replace operation must target a single node:

Expression1

Identifies a node whose value is to be updated. It must identify only a single node. That is, Expression1 must be a static singleton. If the XML is typed, the type of the node must be a simple type.

This means that to replace the contents of two separate nodes you will need two separate operations.

SQL Server also supports two XQuery Extension Functions, sql:column() and sql:variable(), which allow you to reference column and variable values from within expressions.

We can utilize sql:variable() to simplify your code to avoid the use of sp_executesql...

declare @LS_NODE_NAME nvarchar(100) = N'user_input_attn_obligee_desc',
        @LS_NAME_DESC  nvarchar(100) = N'EDWIN CHAND',
        @LS_DOCUMENT_XML xml = N'<root>
  <first>
    <var name="user_input_attn_obligee_desc">OBLIGEE ATTORNEY</var>
  </first>
  <second>
    <var name="user_input_attn_obligee_desc">saravanan</var>
  </second>
  <user_input_attn_obligor_desc>OBLIGOR ATTORNEY</user_input_attn_obligor_desc>
</root>';

set @LS_DOCUMENT_XML.modify(N'
  replace value of (/root/first/var[@name=sql:variable("@LS_NODE_NAME")]/text())[1]
  with sql:variable("@LS_NAME_DESC")
  ');

set @LS_DOCUMENT_XML.modify(N'
  replace value of (/root/second/var[@name=sql:variable("@LS_NODE_NAME")]/text())[1]
  with sql:variable("@LS_NAME_DESC")
  ');

select @LS_DOCUMENT_XML;

This yields the result:

<root>
    <first>
        <var name="user_input_attn_obligee_desc">EDWIN CHAND</var>
    </first>
    <second>
        <var name="user_input_attn_obligee_desc">EDWIN CHAND</var>
    </second>
    <user_input_attn_obligor_desc>OBLIGOR ATTORNEY</user_input_attn_obligor_desc>
</root>

To address subsequent comments from the OP...

The XPath query used in XML modify() has a number of limitations that prevent you from using simple solutions such as alternating paths (/root/(first,second)/var[@name=sql:variable("@LS_NODE_NAME")]/text()[1]) or replacing [1] with something like [sql:variable("@NodeIndex")] in a while loop.

If you don't know ahead of time how many nodes need to be matched, or the specific XPath for all of the elements, it is possible to use the // (descendant-or-self) axis specifier along with the XML exist() method and then simply replace the text() for nodes that don't already match, such as with the following SQL...

declare @ModifyCount int = 0;
while (@ModifyCount < 10 and @LS_DOCUMENT_XML.exist('//var[@name=sql:variable("@LS_NODE_NAME")][text()!=sql:variable("@LS_NAME_DESC")]') = 1)
begin
  set @LS_DOCUMENT_XML.modify(N'
    replace value of (//var[@name=sql:variable("@LS_NODE_NAME")][text()!=sql:variable("@LS_NAME_DESC")]/text())[1]
    with sql:variable("@LS_NAME_DESC")
    ');
  set @ModifyCount += 1;
end
select @ModifyCount, @LS_DOCUMENT_XML;

The problems with this, though, include:

  1. //var isn't paying attention to any ascendant node hierarchy, so may not always target the path(s) you expect.
  2. So as to avoid infinite loops do not trust exist() by itself - always include a Guard Counter such as the @ModifyCount with its limit of 10 recursions.

TSQL Xpath Modify XML string

To answer your specific question, "why isn't it replacing the account number", it's because XML function parameters must be string literals. You are constructing the parameter to the modify XML function, which is causing an error condition.

Normally, SQL Server throws an error when you aren't using a string literal in your XML function parameter, but in this case, it must be confused because of the nodes() function call.

Here is a simple example with a string literal which works, as you point out in your comment:

DECLARE @xml XML = '<optional><account>155555555</account></optional>'
SET @xml.modify('replace value of (/optional/account/text())[1] with "1"');
SELECT @xml;

However, if you try to construct the modify XML function parameter, as in the below example, it will fail:

DECLARE @xml XML = '<optional><account>155555555</account></optional>'
SET @xml.modify('replace value of (/optional/account/text())[1] with "' + '1' + '"');
SELECT @xml;

The error is:

Msg 8172, Level 16, State 1, Line 2 The argument 1 of the XML data
type method "modify" must be a string literal.

Apparently, if you get a little fancier by throwing the nodes() in there, it confuses SQL Server and squelches the error condition:

DECLARE @xml XML = '<optional><account>155555555</account></optional>'
SET @xml.modify('replace value of (/optional/account/text())[1] with "' + (SELECT '1' FROM @xml.nodes('/optional/')) + '"');
SELECT @xml;

That doesn't error, but rather terminates before displaying any data and simply prints:

Command(s) completed successfully.

So you can't really construct the XML function parameter. However, you can still use outside information. Kiran Hedge showed how by using the sql:variable() XQuery extension function.

However, you don't actually have to go to that length. You are using data from inside the XML, which the XML processor has native access to. So you can do something like this:

DECLARE @newXmlSingleReplacement XML = '<optional><account>155555555</account></optional>';
SET @newXmlSingleReplacement.modify('replace value of ((/optional/account)/text())[1] with fn:concat("ABC-",((/optional/account)/text())[1])');
SELECT @newXmlSingleReplacement;

So either Kiran's or this solution works fine for your simplified example. But you probably have a more complex XML document. It probably has multiple "rows" which you'd like to change.

If you try the same code from above with an XML document with multiple account numbers, only the first number is replaced:

DECLARE @newXmlSingleReplacement XML ='<optional><account>155555555</account></optional><optional><account>255555555</account></optional>';
    SET @newXmlSingleReplacement.modify('replace value of ((/optional/account)/text())[1] with fn:concat("ABC-",((/optional/account)/text())[1])');
    SELECT @newXmlSingleReplacement;

Results:

<optional>
  <account>ABC-155555555</account>
</optional>
<optional>
  <account>255555555</account>
</optional>

You might think you could simply remove the index (1) and affect all of the rows. Unfortunately, according to Microsoft's documentation, the first parameter of replace value of "must identify only a single node". So you can't take a list of all of the account numbers and operate on that.

This example:

DECLARE @newXmlSingleReplacement XML ='<optional><account>155555555</account></optional><optional><account>255555555</account></optional>';
SET @newXmlSingleReplacement.modify('replace value of ((/optional/account)/text()) with fn:concat("ABC-",((/optional/account)/text()))');
SELECT @newXmlSingleReplacement;

Results in this error:

Msg 2389, Level 16, State 1, Line 2 XQuery [modify()]: 'concat()'
requires a singleton (or empty sequence), found operand of type
'xdt:untypedAtomic *'

So instead, you could loop over all of your "rows" and perform the modify() operation each time. You would need a way to track your progress with a counter. Here is an example of that, using slightly more complex XML to prove up the concept.

DECLARE @xml XML = '<optional><other>Test123</other><account>155555555</account></optional><optional><other>Test321</other><account>255555555</account></optional>';
DECLARE @newxml XML = @xml, @AccountCount int = 0, @Counter int = 0;
SET @AccountCount = @newxml.value('fn:count(//account)','int');
WHILE @Counter <= @AccountCount
BEGIN
    SET @Counter = @Counter + 1;
    SET @newxml.modify('replace value of ((/optional/account)[position()=sql:variable("@Counter")]/text())[1] with fn:concat("ABC-",((/optional/account)[position()=sql:variable("@Counter")]/text())[1])');
END
SELECT  @newxml;

Results:

<optional>
  <other>Test123</other>
  <account>ABC-155555555</account>
</optional>
<optional>
  <other>Test321</other>
  <account>ABC-255555555</account>
</optional>

Of course we would prefer to avoid loops in SQL code, if we can. Single statements which operate on sets often yield better performance.

One option is to shred your XML and reform it, while adjusting the values in the process. The disadvantage of this method is that you must know the specifics of the XML in order to reconstruct it. It might also be an expensive and convoluted statement, depending on the complexity of the XML document. Here is an example:

DECLARE @xml XML = '<optional><other>Test123</other><account>155555555</account></optional><optional><other>Test321</other><account>255555555</account></optional>';
SELECT
    v.value('(./other)[1]','varchar(500)') AS other,
    'ABC-' + v.value('(./account)[1]','varchar(500)') AS account
FROM        @xml.nodes('/optional') AS T(v)
FOR XML PATH ('optional'), TYPE;

But that's not the only way to reform the XML. You could reconstruct it using the XML system itself and its FLWOR statement support. For example:

DECLARE @xml XML = '<optional><other>Test123</other><account>155555555</account></optional><optional><other>Test321</other><account>255555555</account></optional>';
SELECT @xml.query ('
    for $optional in //optional
    return
        <optional>
            {$optional/other}
            <account>ABC-{$optional/account/text()}</account>
        </optional>

');

But again, that requires knowing and manually recreating the structure of the XML. There are ways to avoid that. The next example requires minimal knowledge of the existing XML. It essentially loops over the nodes at the level of the account node and replaces them only if they are named "account".

DECLARE @xml XML = '<optional><other>Test123</other><account>155555555</account></optional><optional><other>Test321</other><account>255555555</account></optional>';
SELECT @xml.query ('
    for $optional in //optional
    return
        <optional>
            {
                for $subnode in $optional/*
                    return
                        if (fn:local-name($subnode) = "account")
                        then    
                            <account>ABC-{$subnode/text()}</account>
                        else
                            $subnode
            }
        </optional>
');

Based on a crude test with SET STATISTICS TIME ON; on these very small example XML documents, it appears that the nodes() shredding and reconstructing is slightly faster. It also has the simplest and lowest cost query plan by a significant margin.


Related Topics

Find the Byte Size of a Row in Postgresql

Notify My Wcf Service When My Database Is Updated

How to Make a Column Invisible in an Ssrs Matrix

Case Statement with Different Data Type

Count Values for Every Column in a Table

Problems with Createdb in Postgres

Is There a Function That Takes a Year, Month and Day to Create a Date in Postgresql

Renaming a Column Without Breaking the Scripts and Stored Procedures

Sql, SQLite Select with Inner Join

Order Guarantee for Identity Assignment in Multi-Row Insert in SQL Server

How to Force Nolock Hint for SQL Server Logins

Inserting Multiple Rows in Sybase Ase

Oracle Equivalent of Rowlock, Updlock, Readpast Query Hints

How to Select Records That Don't Exist in SQL Server

Recursive Select

SQL Trigger After Insert Update Another Table with Conditions

Find Out the Calling Stored Procedure in SQL Server

Bool Support Oracle SQL


Leave a reply



Submit