Início / dba / Perguntas / 77362
Accepted
SQLBen
Asked: 2014-09-24 02:42:20 +0800 CST

Extraindo um valor inteiro de uma coluna xml

  • 772

Só estou querendo saber como posso extrair um número inteiro de uma coluna XML. Atualmente, todos os dígitos são 4, o que a consulta abaixo é capaz de fazer. Mas o número vai crescer para 10.000 em breve, o que significará 5 dígitos e esta consulta não será capaz de fazê-lo. Existe alguma maneira de obter dinamicamente qualquer número inteiro que esteja próximo no meio <siteID>e </siteID>da coluna Xml. Qualquer sugestão será muito apreciada.

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 respostas

  1. Best Answer

    Você deve usar o método value() do tipo de dados XML.

    Se sua estrutura XML se parece com isso:

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

    Sua consulta seria

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

    Mas, no entanto, não parece que você tenha uma coluna XML, pois msg like '<DLR%'é inválida nas colunas XML.

    Em seguida, você precisa modificar sua tabela para que a coluna seja XML ou pode converter para XML em sua consulta.

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

  2. Se seus dados não estiverem tão limpos, a função XQuery fn:number é realmente muito boa para limpá-los para você. Experimente esta demonstração simples:

    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. Se o MS SQL Server for o que você está usando, ele oferece suporte à consulta XPath na instrução SELECT. Veja aqui um exemplo e aqui está uma pergunta SO.

    Isso permitirá que você extraia os numerais de dentro do xml, independentemente de quantos dígitos ele contém. Você pode então CONVERT()fazê-lo intou o que for necessário.

    • 0

relate perguntas