Could anyone help on how to remove white spaces from an XML file?

I used the following code that i got from Mikael Eriksson on Stack Exchange to extract the site id:

cast(msg as xml).value('(/DLR_NOTIFICATION/siteid/text())[1]', 'int') AS SiteID

from the following xml:

  <siteid> 7929 </siteid>
  <purpose>IMPerial NOTIFICATION</purpose>

How do I trim the whitespaces or any unwanted characters? Or if there is any better way of dealing with this?

I realised that some of this XML have got whitespaces within as shown above in the siteid had caused the below error message

Msg 245, Level 16, State 1, Line 100 Conversion failed when converting the nvarchar value ' 7929 ' to data type int.

  • SQL Server? Please tag your question for the DBMS you're using. – Colin 't Hart Sep 24 '14 at 8:16
  • the production database is Sql 2005 – SQLBen Sep 24 '14 at 8:20
  • @Mikael Eriksson the below is one of the few that are causing the problems. I just sql fiddled it now and it brings the same error message: <DLR_NOTIFICATION> <kid>123456</kid> <siteid> 7913</siteid> <transid> c4888def-94ea-4fc5-9317-2b686ce24289</transid> <service>01234</service> <mobile>44750000000</mobile> <msisdntype>IPAlias</msisdntype> <operator>O2</operator> <status>100</status> <tariff>600</tariff> <billingroute>Imp</billingroute> <purpose>IMP NOTIFICATION</purpose> </DLR_NOTIFICATION> – SQLBen Sep 24 '14 at 9:31
  • Can you post a link to the fiddle that shows the error? – Mikael Eriksson Sep 24 '14 at 9:45
  • You can use the XQuery function fn:number to whitespace cleaning for you: dba.stackexchange.com/questions/77362/… – wBob Sep 24 '14 at 10:55

This actually does the job by changing int to nvarchar(10) from the script that was posted by Mikael Eriksson:

select cast(msg as xml).value('(/DLR_NOTIFICATION/siteid/text())[1]','nvarchar(10)')SiteID
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.