Solved

Replace NULL value with Blank space in Xquery

Posted on 2010-08-24
7
1,610 Views
Last Modified: 2013-11-11
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
Comment
Question by:sureshbabukrish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
7 Comments
 
LVL 4

Assisted Solution

by:MSSystems
MSSystems earned 100 total points
ID: 33518531
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33518594
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 400 total points
ID: 33518620
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33632784
Solution was provided which is valid and solves the question - useful for PAQ database.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33634005
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
 
LVL 9

Author Closing Comment

by:sureshbabukrish
ID: 33634044
the answers were  useful
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
microsoft access - xml 10 82
Fulfillment API php code sample 1 96
XML loaded in a form with dropdown 6 57
CRM 2011 Advanced Find Producing Error 3 43
The Problem How to write an Xquery that works like a SQL outer join, providing placeholders for absent data on the outer side?  I give a bit more background at the end. The situation expressed as relational data Let’s work through this.  I’ve …
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Suggested Courses

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question