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>
            <b>NA</b>
      </Test1>
      <Test1>
            <a>NA</a>
            <b>NA</b>
      </Test1>
      <Test1>
            <a>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")')

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.
LVL 9
sureshbabukrishAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
This targetting is more accurate
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)[1]/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)[2]/text())[1] 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)[3]/text())[1] 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

0
 
MSSystemsConnect With a Mentor Commented:
Maybe you can try this from:
http://www.w3schools.com/Xpath/xpath_functions.asp

fn:replace(string,pattern,replace) 

Returns a string that is created by replacing the given pattern with the replace argument
Example: replace("Bella Italia", "l", "*")
Result: 'Be**a Ita*ia'

Example: replace("Bella Italia", "l", "")
Result: 'Bea Itaia' 

Open in new window

0
 
cyberkiwiCommented:
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

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
cyberkiwiCommented:
Solution was provided which is valid and solves the question - useful for PAQ database.
0
 
cyberkiwiCommented:
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.
0
 
sureshbabukrishAuthor Commented:
the answers were  useful
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.