none
Remove XML Tags with White space. RRS feed

  • Question

  • Hi All, I have a scenario like the below code where I have empty spaces in few columns of my tables. I create XML of the data and send it. There is a lot of code such and I can't add ISNULL() on each and every column. Is there any simple one line code working with the XML so that I can remove the tags with only empty spaces. TIA.

    DECLARE @xml1 VARCHAR(max)
    DECLARE @xml2 VARCHAR(max)
    
    set @xml1 = (SELECT 1 AS Col11, ' ' AS Col21, 'ABC' AS Col31 FOR XML RAW, ELEMENTS)  
    set @xml2 = (SELECT 2 AS Col12, ' ' AS Col22, 'ABC' AS Col32 FOR XML RAW, ELEMENTS)
    
    
    DECLARE @xml xml;  
    SET @xml = Cast('<ROOT>' + ISNULL(@xml1, '') + ISNULL(@xml2, '') + '</ROOT>' as xml)  
      
    SET @xml.modify('delete //*[not(node())]');
    
    -- Some Way to Remove the XML Tags with Empty space here.
      
    select @xml as XMLString  
    
    /* Output of the Above script.
    <ROOT>
      <row>
        <Col11>1</Col11>
        <Col21> </Col21> -- This XML Tag with a white space should be removed
        <Col31>ABC</Col31>
      </row>
      <row>
        <Col12>2</Col12>
        <Col22> </Col22> -- This XML Tag with a white space should be removed
        <Col32>ABC</Col32>
      </row>
    </ROOT>
    */



    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Tuesday, May 1, 2012 6:06 AM

Answers

  • It would have helped if there had been a trim() function in XQuery.

    With some trial and error and inventive thinking I came up with this:

    SET @xml.modify('delete //*[. <= " "]');

    As they say: it seems to work. But I leave no warranties.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 1, 2012 10:04 AM

All replies

  • DECLARE @xml1 VARCHAR(max)
    DECLARE @xml2 VARCHAR(max)
    
    set @xml1 = (SELECT 1 AS Col11, LTRIM(RTRIM(' ')) AS Col21, 'ABC' AS Col31 FOR XML RAW, ELEMENTS)  
    set @xml2 = (SELECT 2 AS Col12, LTRIM(RTRIM(' ')) AS Col22, 'ABC' AS Col32 FOR XML RAW, ELEMENTS)
    
    
    DECLARE @xml xml;  
    SET @xml = Cast('<ROOT>' + ISNULL(@xml1, '') + ISNULL(@xml2, '') + '</ROOT>' as xml)  
      
    SET @xml.modify('delete //*[not(node())]');
    
    -- Some Way to Remove the XML Tags with Empty space here.
      
    select @xml as XMLString  
    
    /* Output of the Above script.
    <ROOT>
      <row>
        <Col11>1</Col11>
        <Col21> </Col21> -- This XML Tag with a white space should be removed
        <Col31>ABC</Col31>
      </row>
      <row>
        <Col12>2</Col12>
        <Col22> </Col22> -- This XML Tag with a white space should be removed
        <Col32>ABC</Col32>
      </row>
    </ROOT>
    */
    


    Please, If answer match your requirement then set mark as answer

    Tuesday, May 1, 2012 6:52 AM
  • Thanks Dhaval, but unfortunately my code is so large that I can't go change all the instances. So this won't work for me.

    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Tuesday, May 1, 2012 6:58 AM
  • It would have helped if there had been a trim() function in XQuery.

    With some trial and error and inventive thinking I came up with this:

    SET @xml.modify('delete //*[. <= " "]');

    As they say: it seems to work. But I leave no warranties.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 1, 2012 10:04 AM
  • It would have helped if there had been a trim() function in XQuery.

    With some trial and error and inventive thinking I came up with this:

    <span style="color:Blue">SET</span> @xml.modify(<span style="color:#A31515">'delete //*[. <= " "]'</span>);
    

    As they say: it seems to work. But I leave no warranties.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wow Erland  this seems to work, I need to test it against my data and update on this. Thanks again.

    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Tuesday, May 1, 2012 1:05 PM
  • As Erland commented, it would be easier if there were a TRIM xQuery function. Be aware that this approach will not catch a node if the length of the string is greater than 1 (more than 1 space).

    DECLARE @xml1 VARCHAR(max);
    DECLARE @xml2 VARCHAR(max);
    
    set @xml1 = (SELECT 1 AS Col11, '  ' AS Col21, 'ABC' AS Col31 FOR XML RAW, ELEMENTS);
    set @xml2 = (SELECT 2 AS Col12, '  ' AS Col22, 'ABC' AS Col32 FOR XML RAW, ELEMENTS);
    
    
    DECLARE @xml xml;  
    SET @xml = Cast('<ROOT>' + ISNULL(@xml1, '') + ISNULL(@xml2, '') + '</ROOT>' as xml);
    
    select @xml as XMLString;
    
    SET @xml.modify('delete //*[.<=" "]');
    
    select @xml as XMLString; 
    GO

    It will be safer if you use NULLIF(LTRIM(column_name), '') or NULLIF(column_name, '') .


    AMB

    Some guidelines for posting questions...

    Tuesday, May 1, 2012 2:04 PM
    Moderator
  • Be aware that this approach will not catch a node if the length of the string is greater than 1 (more than 1 space).

    Drat! I thought I tested that, but I see now that I had reverted back to single space before test. But assuming that Arun does not have umpteen spaces betweens his tags, this may work:

    SET @xml.modify('delete //*[. <= "                                        "]');

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 1, 2012 4:19 PM
  • Be aware that this approach will not catch a node if the length of the string is greater than 1 (more than 1 space).

    Drat! I thought I tested that, but I see now that I had reverted back to single space before test. But assuming that Arun does not have umpteen spaces betweens his tags, this may work:

    <span style="color:blue">SET</span> @xml.modify(<span style="color:#a31515">'delete //*[. <= "                                        "]'</span>);
    

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sweet!!!

    Just for fun, we could use a variable to make it more readable.

    declare @s varchar(8000) = space(8000);
    ...SET @xml.modify('delete //*[.<=sql:variable("@s")]');
    ...


    AMB

    Some guidelines for posting questions...

    Tuesday, May 1, 2012 5:16 PM
    Moderator
  • Thanks all.

    For me in almost all cases, I get a single space but however, I would go with 3 spaces to be safe. Will using such a huge empty string as in above case won't cost more in performance.


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Thursday, May 3, 2012 10:11 AM
  • Hi all,

    SET @xml.modify('delete //*[. <= " "]'); works great if the string is one character wide (" "), but any thoughts on removing any strings that are all whitespace?

    For example, this node will not be removed:

    <TRCM>    </TRCM>

    TIA,

    Dave

    Wednesday, October 1, 2014 1:50 PM