Extracting an an integer value from an xml column

Question

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> and </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

Answer 1

You should use the value() method of the XML data type.

If your XML structure looks like this:

<DLR>
  <siteID>12345</siteID>
</DLR>

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 '<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

Answer 2

If your data is not that clean, then XQuery function fn:number is actually quite good at cleaning it for you. Try this simple demo:

IF OBJECT_ID('tempdb..#yourTable') IS NOT NULL DROP TABLE #yourTable
CREATE TABLE #yourTable ( rowId INT IDENTITY PRIMARY KEY, msg VARCHAR(8000) )

INSERT INTO #yourTable ( msg ) VALUES ( '<DLR_NOTIFICATION><siteid>29   </siteid>trailing tab</DLR_NOTIFICATION>' )
INSERT INTO #yourTable ( msg ) VALUES ( '<DLR_NOTIFICATION><siteid> 101</siteid>leading tab</DLR_NOTIFICATION>' )
INSERT INTO #yourTable ( msg ) VALUES ( '<DLR_NOTIFICATION><siteid>
2001</siteid>carriage return</DLR_NOTIFICATION>' )

INSERT INTO #yourTable ( msg ) VALUES ( '<DLR_NOTIFICATION>
    <kid>204122</kid>
    <siteid> 1234
    </siteid>
    <transid>1************</transid>
    <service>20462</service>
    <mobile>447*******</mobile>
    <msisdntype>IPAlias</msisdntype>
    <operator>
        Three
    </operator>
    <status>100</status>
    <tariff>0000</tariff>
    <billingroute>
        I******
    </billingroute>
    <purpose>IMPULSEPAY DLR NOTIFICATION</purpose>
    <tracking>
        <aff>14_z</aff>
        <param>undefined</param>
        <paramdetails>
            kp=xkpgbadu200000000000000000000000059;
        </paramdetails>
    </tracking>
</DLR_NOTIFICATION>' )

SELECT s.c.value('fn:number(.)', 'INT' ) AS siteid
FROM ( SELECT CAST( msg AS XML ) AS msg FROM #yourTable ) a
    CROSS APPLY a.msg.nodes('DLR_NOTIFICATION/siteid') s(c)

Answer 3

If MS SQL Server is what you're using, it supports XPath query within the SELECT statement. See here for an example and here's an SO question.

This will allow you to extract the numerals from within the xml irrespective of how many digits it contains. You can then CONVERT() it to int or whatever as needs be.