Extracting an an integer value from an xml column I'm just wondering how I can extract an integer out of an XML column. Currently all the digits are 4 which the below query is able to do. But the number is going to grow to 10,000's soon which will mean 5 digit and this query will not be able to do it. Is there anyway that I can dynamically get any integer out that is next in the middle of &lt;siteID&gt; and &lt;/siteID&gt; from the Xml column. Any suggestion will be highly appreciated.

SELECT 
SUBSTRING(msg, patindex('%[0-9][0-9][0-9][0-9]&lt;/siteID&gt;%',msg),4)  AS DOMAIN
FROM table  a(NOLOCK)
WHERE msg like '&lt;DLR%'
ORDER BY 1 DESC
You should use the value() method of the XML data type.

If your XML structure looks like this:

&lt;DLR&gt;
  &lt;siteID&gt;12345&lt;/siteID&gt;
&lt;/DLR&gt;


Your query would be

select msg.value('(/DLR/siteID/text())[1]', 'int') as DOMAIN
from dbo.YourTable


But it does however not look like you have an XML column at all since msg like '&lt;DLR%' is invalid against XML columns.

Then you need to either modify your table so the column is XML or you can cast to XML in your query.

select cast(msg as xml).value('(/DLR/siteID/text())[1]', 'int') as DOMAIN
from dbo.YourTable