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
</siteID> from the Xml column. Any suggestion will be highly appreciated.
SELECT SUBSTRING(msg, patindex('%[0-9][0-9][0-9][0-9]</siteID>%',msg),4) AS DOMAIN FROM table a(NOLOCK) WHERE msg like '<DLR%' ORDER BY 1 DESC