asked on

Replace NULL value with Blank space in Xquery

DECLARE @xml XML
 ,@aValue VARCHAR(10)
 ,@bValue VARCHAR(10)
 ,@cValue VARCHAR(10)
 ,@dValue VARCHAR(10)
 ,@eValue VARCHAR(10)
 ,@fValue VARCHAR(10)

SET @xml =
'<Test>
 <Test1>
 <a>NA</a>
 NA
 </Test1>
 <Test1>
 <a>NA</a>
 NA
 </Test1>
 <Test1>
 <a>NA</a>
 NA
 </Test1>
</Test>'

SELECT
 @aValue = COALESCE(NULL,'')
 ,@bValue = COALESCE(25,'')
 ,@cValue = COALESCE(NULL,'')
 ,@dValue = COALESCE(50,'')
 ,@eValue = COALESCE(NULL,'')
 ,@fValue = COALESCE(100,'')

SET @xml.modify('replace value of (//Test/Test1/a/text())[1]
 with sql:variable("@aValue")')

SET @xml.modify('replace value of (//Test/Test1/b/text())[1]
 with sql:variable("@bValue")')

SET @xml.modify('replace value of (//Test/Test1/a/text())[2]
 with sql:variable("@cValue")')

SET @xml.modify('replace value of (//Test/Test1/b/text())[2]
 with sql:variable("@dValue")')

SET @xml.modify('replace value of (//Test/Test1/a/text())[3]
 with sql:variable("@eValue")')

SET @xml.modify('replace value of (//Test/Test1/b/text())[3]
 with sql:variable("@fValue")')

SELECT @xml

I need to replace the NULL value with empty space. But '' works as the escape character and hence NA is not replaced at node level 2 (@cValue). Please suggest.

SOLUTION

MSSystems

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

Because you are running it sequentially, what is happening is that when it reaches

SET @xml.modify('replace value of (//Test/Test1/a/text())[2] with sql:variable("@cValue")')

a/text()[2] is actually hitting the <a id="3"> node.

By the time it gets to

(//Test/Test1/a/text())[3] with sql:variable("@eValue")

There is no "3rd" element of a/text().

DECLARE @xml XML
      ,@aValue VARCHAR(10)
      ,@bValue VARCHAR(10)
      ,@cValue VARCHAR(10)
      ,@dValue VARCHAR(10)
      ,@eValue VARCHAR(10)
      ,@fValue VARCHAR(10)
SET @xml =
'<Test>
      <Test1>
            <a id="1">NA</a>
            <b>NA</b>
      </Test1>
      <Test1>
            <a id="2">NA</a>
            <b>NA</b>
      </Test1>
      <Test1>
            <a id="3">NA</a>
            <b>NA</b>
      </Test1>
</Test>'
                  
SELECT
      @aValue = COALESCE(NULL,'')
      ,@bValue = COALESCE(25,'')
      ,@cValue = COALESCE(NULL,'')
      ,@dValue = COALESCE(50,'')
      ,@eValue = COALESCE(NULL,'')
      ,@fValue = COALESCE(100,'')
      
SET @xml.modify('replace value of (//Test/Test1/a/text())[1] with sql:variable("@aValue")')            

SET @xml.modify('replace value of (//Test/Test1/b/text())[1] with sql:variable("@bValue")')

select 'There are only two NAs', @xml.query('(//Test/Test1/a/text())')
SET @xml.modify('replace value of (//Test/Test1/a/text())[2] with sql:variable("@cValue")')

SET @xml.modify('replace value of (//Test/Test1/b/text())[2] with sql:variable("@dValue")')

SET @xml.modify('replace value of (//Test/Test1/a/text())[3] with sql:variable("@eValue")')            

SET @xml.modify('replace value of (//Test/Test1/b/text())[3] with sql:variable("@fValue")')

SELECT @xml

Open in new window

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

Solution was provided which is valid and solves the question - useful for PAQ database.

cyberkiwi

http:#a33518594 describes the problem and why sequential updating won't work, especially with a path-positional based approach like XQuery XPath.

http:#a33632784 provides a solution to perform the required updates.

Split between the two.

sureshbabukrish

ASKER

the answers were useful