1

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
  • Can you post the XML schema? – dbafromthecold Sep 23 '14 at 11:30
  • 1
    What version of SQL Server are you using ( eg 2000, 2014 ) and what data-type is the msg column? Also please post a small sample of your xml. thanks – wBob Sep 23 '14 at 11:35
  • This question appears to be off-topic because it is about xml and belongs on SO – Jack Douglas Dec 24 '14 at 9:24
4

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
0

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.

0

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)

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