locked
Remove numeric characters from xml data with data type nvarchar(max) RRS feed

  • Question

  • Hi All,

    I want to remove the numeric characters from the data type which is nvarchar(max) which contains xml_data. Below is an example. There are other product code with similar example as well.

    <Hospital>

    <Assessment>

    <productCode>Cas234p</productCode>

    </Assessment>

    <Hospital>

    Any help here would be appreciated.


    -kccrga http://dbatrend.blogspot.com.au/

    Monday, May 19, 2014 1:35 AM

Answers

  • I have fixed namespace by using the below query.

    declare @t table(x xml)insert @tvalues('<Hospital><Assessment><productCode>Cas234p</productCode></Assessment></Hospital>') 

    UPDATE tSET x.modify('replace value of ((/*:/Hospital/Assessment/productCode/text())[1]) with (sql:column("ModifiedCol"))')

    FROM(SELECT STUFF(m.n.value('productCode[1]','varchar(100)'),PATINDEX('%[A-za-z][0-9]%',

    m.n.value('productCode[1]','varchar(100)'))+1,PATINDEX('%[0-9][A-za-z]%',m.n.value('productCode[1]','varchar(100)'))-PATINDEX('%[A-za-z][0-9]%',m.n.value('productCode[1]','varchar(100)')),'') AS ModifiedCol, xFROM @tCROSS APPLY x.nodes('/*:/Hospital/Assessment')m(n))tSELECT * from @t



    -kccrga http://dbatrend.blogspot.com.au/

    • Marked as answer by Mike Yin Wednesday, May 28, 2014 3:52 PM
    Friday, May 23, 2014 1:16 AM

All replies

  • DECLARE @xml AS xml
    SET @xml='<Hospital>
    <Assessment>
    <productCode>Cas234p</productCode>
    </Assessment>
    </Hospital>'

    SELECT CAST(dbo.ufn_onlychar (CAST(@xml AS NVARCHAR(MAX))) AS XML)


    ---SELECT PATINDEX('%[0-9]%',CAST(@xml AS NVARCHAR(MAX))) AS c

    CREATE FUNCTION dbo.ufn_onlychar (@StrVal AS VARCHAR(8000))
    RETURNS VARCHAR(8000)
    AS
    BEGIN
      WHILE PATINDEX('%[^a-z]%', @StrVal) > 0
        SET @StrVal = REPLACE(@StrVal,
            SUBSTRING(@StrVal,PATINDEX('%[^a-z]%', @StrVal),1),'')
      RETURN @StrVal
    END
    GO

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 19, 2014 6:44 AM
  • You can also do it inline within xml

    declare @t table
    (
    x xml
    )
    insert @t
    values('<Hospital><Assessment><productCode>Cas234p</productCode></Assessment></Hospital>')
    
    
    
    
    UPDATE t
    SET x.modify('replace value of ((/Hospital/Assessment/productCode/text())[1]) with (sql:column("ModifiedCol"))')
    FROM
    (
    SELECT STUFF(m.n.value('productCode[1]','varchar(100)'),PATINDEX('%[A-za-z][0-9]%',m.n.value('productCode[1]','varchar(100)'))+1,PATINDEX('%[0-9][A-za-z]%',m.n.value('productCode[1]','varchar(100)'))-PATINDEX('%[A-za-z][0-9]%',m.n.value('productCode[1]','varchar(100)')),'') AS ModifiedCol, x
    FROM @t
    CROSS APPLY x.nodes('/Hospital/Assessment')m(n)
    )t
    
    SELECT * from @t


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, May 19, 2014 7:38 AM
  • Thank you very much for your reply.

    But I need to update the table as Casp or other value only for product code. There are other values before after the productCode which also has numeric values. Any help would be appreciated.

    SET @xml='<Hospital>

    < Assessment>

    <Type>3</Type>

    < productCode>Cas234p</productCode>

    <AssessmentCode>13434</AssessmentCode>

    < /Assessment>
    < /Hospital>'


    -kccrga http://dbatrend.blogspot.com.au/

    Monday, May 19, 2014 7:40 AM
  • Thank you very much for your reply.

    But I need to update the table as Casp or other value only for product code. There are other values before after the productCode which also has numeric values. Any help would be appreciated.

    SET @xml='<Hospital>

    < Assessment>

    <Type>3</Type>

    < productCode>Cas234p</productCode>

    <AssessmentCode>13434</AssessmentCode>

    < /Assessment>
    < /Hospital>'


    -kccrga http://dbatrend.blogspot.com.au/

    Sorry did you mean you need to remain other values as is. If yes, then posted script work fine. Or do you want to replace them as well?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, May 19, 2014 8:04 AM
  • Thanks for your reply. I meant the other values should stay as it is.

    I have namespace which is making it difficult for me to replace.
    <tns:ClaimResponse xmlns:tns="http://test/online/read/version-2">


    -kccrga http://dbatrend.blogspot.com.au/

    Monday, May 19, 2014 11:36 PM
  • I have fixed namespace by using the below query.

    declare @t table(x xml)insert @tvalues('<Hospital><Assessment><productCode>Cas234p</productCode></Assessment></Hospital>') 

    UPDATE tSET x.modify('replace value of ((/*:/Hospital/Assessment/productCode/text())[1]) with (sql:column("ModifiedCol"))')

    FROM(SELECT STUFF(m.n.value('productCode[1]','varchar(100)'),PATINDEX('%[A-za-z][0-9]%',

    m.n.value('productCode[1]','varchar(100)'))+1,PATINDEX('%[0-9][A-za-z]%',m.n.value('productCode[1]','varchar(100)'))-PATINDEX('%[A-za-z][0-9]%',m.n.value('productCode[1]','varchar(100)')),'') AS ModifiedCol, xFROM @tCROSS APPLY x.nodes('/*:/Hospital/Assessment')m(n))tSELECT * from @t



    -kccrga http://dbatrend.blogspot.com.au/

    • Marked as answer by Mike Yin Wednesday, May 28, 2014 3:52 PM
    Friday, May 23, 2014 1:16 AM
  • I have fixed namespace by using the below query.

    declare @t table(x xml)insert @tvalues('<Hospital><Assessment><productCode>Cas234p</productCode></Assessment></Hospital>') 

    UPDATE tSET x.modify('replace value of ((/*:/Hospital/Assessment/productCode/text())[1]) with (sql:column("ModifiedCol"))')

    FROM(SELECT STUFF(m.n.value('productCode[1]','varchar(100)'),PATINDEX('%[A-za-z][0-9]%',

    m.n.value('productCode[1]','varchar(100)'))+1,PATINDEX('%[0-9][A-za-z]%',m.n.value('productCode[1]','varchar(100)'))-PATINDEX('%[A-za-z][0-9]%',m.n.value('productCode[1]','varchar(100)')),'') AS ModifiedCol, xFROM @tCROSS APPLY x.nodes('/*:/Hospital/Assessment')m(n))tSELECT * from @t



    -kccrga http://dbatrend.blogspot.com.au/


    Hi Kccrga, do you mean the issue has been resolved?

    Regards, Leo

    Friday, May 23, 2014 1:00 PM
  • Yes it is resolved.

    -kccrga http://dbatrend.blogspot.com.au/

    Tuesday, May 27, 2014 7:13 AM