主页 / dba / 问题 / 77362
Accepted
SQLBen
Asked: 2014-09-24 02:42:20 +0800 CST

从 xml 列中提取整数值

  • 772

我只是想知道如何从 XML 列中提取整数。目前所有数字都是 4，下面的查询可以做到。但是这个数字很快就会增长到 10,000 个，这意味着 5 位数，这个查询将无法做到这一点。无论如何，我可以动态地从 Xml 列的中间<siteID>和中间取出任何整数。</siteID>任何建议将不胜感激。

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
sql-server t-sql

3 个回答

  1. Best Answer

    您应该使用 XML 数据类型的value() 方法。

    如果您的 XML 结构如下所示：

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

    您的查询将是

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

    但它看起来根本不像您有一个 XML 列，因为它对 XML 列msg like '<DLR%'无效。

    然后您需要修改您的表以使该列是 XML，或者您可以在查询中转换为 XML。

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

  2. 如果您的数据不是那么干净，那么 XQuery 函数 fn:number 实际上非常适合为您清理它。试试这个简单的演示：

    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)
    • 1

  3. 如果您使用的是 MS SQL Server，它支持 SELECT 语句中的 XPath 查询。有关示例，请参见此处，这是一个 SO 问题。

    这将允许您从 xml 中提取数字，而不管它包含多少数字。然后你可以CONVERT()根据int需要或任何需要。

    • 0

相关问题