Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1785
  • Last Modified:

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.
0
sureshbabukrish
Asked:
sureshbabukrish
  • 4
2 Solutions
 
MSSystemsCommented:
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
 
cyberkiwiCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now