Remove From My Forums

Query campo xml utilizando sql server 2008

Pregunta

Inicie sesión para votar

Saludos,

Hace unos cuantos días me he encontrado con la necesidad de generar un reporte, donde en un campo de la base de dato guardo un archivo XML que es el siguiente:

<Request><Action>RegistraPagoSFB</Action><NS1:RegistraPagoSFBReq xmlns:NS1="xmlns"><Param><NumeroAutorizacion>0620151629385165</NumeroAutorizacion><Monto>804</Monto><IdCuenta>100010130022373</IdCuenta><Descripcion>TSS 06-2015</Descripcion><Recibo>9292849</Recibo><Mensaje>Factura Autorizada</Mensaje></Param><CONTROL><APP_CODE>NetBankingEmpresas</APP_CODE></CONTROL></NS1:RegistraPagoSFBReq></Request>

y tengo esta consulta :

Declare @TablaCaracteres varchar(500), @TablaConsulta varchar(500),  @XML XML, @Tabla1 varchar(100), @Tabla2 varchar(100), @Tabla3 varchar(100)


 SET @TablaConsulta =( SELECT Request  from [dbo].[Transactions] where transactionid = 34)
 SET @TablaCaracteres = @TablaConsulta

 DECLARE @TablaAuxiliar varchar(500) = replace(@TablaCaracteres,':','')


 SET @XML= @TablaAuxiliar

 SELECT XMLTABLE.XMLROW.value('(NumeroAutorizacion) [1]', 'varchar(100)' ) as 'NumeroAutorizacion',
        XMLTABLE.XMLROW.value('(Monto) [1]', 'varchar(100)' ) as 'Monto',
		XMLTABLE.XMLROW.value('(IdCuenta) [1]', 'varchar(100)' ) as 'IdCuenta'
		FROM @XML.nodes('/Request/NS1RegistraPagoSFBReq/Param') XMLTABLE(XMLROW)

Que me devuelve este resultado :

Lo que necesito saber es como hago desde una consulta sql como esta que tengo, o en este mismo query, obtener un valor de mi campo que contiene el xml y pasarselo a la consulta, me gustaria pasarle el campo <NumeroAutorizacion>, que esta en mi XML, como haria eso, gracias

Si mi respuesta te ha ayudado a resolver tus problemas, Selecciona "Proponer como respuesta"

Blog
J.Joaquin

lunes, 20 de julio de 2015 2:44

Respuestas

Inicie sesión para votar

Hombre, no nos haga trabajar a ciegas y ponga un ejemplo de la tabla, setencias INSERT y resultado esperado.

Aca un ejemplo:

DECLARE @T table (
TransactionID int,
Request xml
);

INSERT INTO @T (TransactionID, Request)
VALUES
    (34, '
<Request>
    <Action>RegistraPagoSFB</Action>
    <NS1:RegistraPagoSFBReq xmlns:NS1="xmlns">
        <Param>
            <NumeroAutorizacion>0620151629385165</NumeroAutorizacion>
            <Monto>804</Monto>
            <IdCuenta>100010130022373</IdCuenta>
            <Descripcion>TSS 06-2015</Descripcion>
            <Recibo>9292849</Recibo>
            <Mensaje>Factura Autorizada</Mensaje>
        </Param>
        <CONTROL>
            <APP_CODE>NetBankingEmpresas</APP_CODE>
        </CONTROL>
    </NS1:RegistraPagoSFBReq>
</Request>'),
    (35, '
<Request>
    <Action>RegistraPagoSFB</Action>
    <NS1:RegistraPagoSFBReq xmlns:NS1="xmlns">
        <Param>
            <NumeroAutorizacion>0620151629385166</NumeroAutorizacion>
            <Monto>800</Monto>
            <IdCuenta>100010130022376</IdCuenta>
            <Descripcion>TSS 06-2015</Descripcion>
            <Recibo>9292850</Recibo>
            <Mensaje>Factura Autorizada</Mensaje>
        </Param>
        <CONTROL>
            <APP_CODE>NetBankingEmpresas</APP_CODE>
        </CONTROL>
    </NS1:RegistraPagoSFBReq>
</Request>'),
(36, '
<Request>
    <Action>RegistraPagoSFB</Action>
    <NS1:RegistraPagoSFBReq xmlns:NS1="xmlns">
        <Param>
            <NumeroAutorizacion>0620151629385167</NumeroAutorizacion>
            <Monto>840</Monto>
            <IdCuenta>100010130022377</IdCuenta>
            <Descripcion>TSS 06-2015</Descripcion>
            <Recibo>9292851</Recibo>
            <Mensaje>Factura Autorizada</Mensaje>
        </Param>
        <CONTROL>
            <APP_CODE>NetBankingEmpresas</APP_CODE>
        </CONTROL>
    </NS1:RegistraPagoSFBReq>
</Request>');


-- todas las transacciones
WITH XMLNAMESPACES ('xmlns' AS NS1)
SELECT
    T.TransactionID,
    T.Request.value('(Request/NS1:RegistraPagoSFBReq/Param/NumeroAutorizacion/text())[1]', 'varchar(50)') AS NumeroAutorizacion
FROM
    @T AS T;
    
-- Autorizacion relacionada a una transaccion en especifico
DECLARE @TransactionID int = 34;

WITH XMLNAMESPACES ('xmlns' AS NS1)
SELECT
    T.TransactionID,
    N.x.value('(text())[1]', 'varchar(50)') AS NumeroAutorizacion
FROM
    @T AS T
    CROSS APPLY
    T.Request.nodes('(Request/NS1:RegistraPagoSFBReq/Param/NumeroAutorizacion)[1]') AS N(x)
WHERE
    T.TransactionID = @TransactionID;

-- Buscar un Autorizacion en especifico
DECLARE @NumeroAutorizacion varchar(50) = '0620151629385167';

WITH XMLNAMESPACES ('xmlns' AS NS1)
SELECT
    T.TransactionID,
    R.NumeroAutorizacion
FROM
    @T AS T
    CROSS APPLY
    (
    SELECT 
        T.Request.value('(Request/NS1:RegistraPagoSFBReq/Param/NumeroAutorizacion/text())[1]', 'varchar(50)') AS NumeroAutorizacion
    ) AS R
WHERE
    R.NumeroAutorizacion = @NumeroAutorizacion;

-- Buscar un Autorizacion en especifico
-- usando xml metodo exist()
WITH XMLNAMESPACES ('xmlns' AS NS1)
SELECT
    T.TransactionID,
    T.Request.value('(Request/NS1:RegistraPagoSFBReq/Param/NumeroAutorizacion/text())[1]', 'varchar(50)') AS NumeroAutorizacion
FROM
    @T AS T
WHERE
    T.Request.exist('Request/NS1:RegistraPagoSFBReq/Param/NumeroAutorizacion[(text()[1]) = sql:variable("@NumeroAutorizacion")]') = 1;
GO

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Propuesto como respuesta Miguel Egea GómezMVP, Moderator lunes, 20 de julio de 2015 15:38
Marcado como respuesta Karen MalagónModerator martes, 21 de julio de 2015 18:07

lunes, 20 de julio de 2015 15:37

Todas las respuestas

0

Inicie sesión para votar
te refieres a algo así
declare @numAtorizacion varchar(100) SELECT @numAtorizacion =XMLTABLE.XMLROW.value('(NumeroAutorizacion) [1]', 'varchar(100)' ) FROM @XML.nodes('/Request/NS1RegistraPagoSFBReq/Param') XMLTABLE(XMLROW)

luego puedes usar el campo @numAtutorización como quieras
Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA
lunes, 20 de julio de 2015 9:42

Responder

|

Citar

Moderador

Inicie sesión para votar

Muchas gracias por responder, pero tengo otra pregunta aqui en mi select es que se la quiero pasar ese valor ya recuperado, como lo hago

 SELECT XMLTABLE.XMLROW.value('(NumeroAutorizacion) [1]', 'varchar(100)' ) as 'NumeroAutorizacion',
        XMLTABLE.XMLROW.value('(Monto) [1]', 'varchar(100)' ) as 'Monto',
		XMLTABLE.XMLROW.value('(IdCuenta) [1]', 'varchar(100)' ) as 'IdCuenta'
		FROM @XML.nodes('/Request/NS1RegistraPagoSFBReq/Param') XMLTABLE(XMLROW)

tengo esto hasta el momento.

Si mi respuesta te ha ayudado a resolver tus problemas, Selecciona "Proponer como respuesta"

Blog
J.Joaquin

lunes, 20 de julio de 2015 14:00

0

Inicie sesión para votar
Hola,

La respuesta proporcionada por Miguel no te ayuda? en la variable @numAutorizacion ya tienes almacenado el valor, de no ser así, puedes decir mayor detalle para ayudarte.

declare @numAutorizacion varchar(100) SELECT @numAutorizacion =XMLTABLE.XMLROW.value('(NumeroAutorizacion) [1]', 'varchar(100)' ), XMLTABLE.XMLROW.value('(Monto) [1]', 'varchar(100)' ) as 'Monto', XMLTABLE.XMLROW.value('(IdCuenta) [1]', 'varchar(100)' ) as 'IdCuenta' FROM @XML.nodes('/Request/NS1RegistraPagoSFBReq/Param') XMLTABLE(XMLROW) SELECT @numAutorizacion

Saludos.
Ayacucho - Perú
Recuerda si mi solución atiende tu consulta por favor márcala como útil y como respuesta.

http://litigiouslobo.blogspot.com/
El Blog de Herbert Mendoza
lunes, 20 de julio de 2015 14:11

Responder

|

Citar
0

Inicie sesión para votar
Saludos Steve,

Me sirvio la respuesta de miguel para capturar el valor de mi campo, ahora necesito pasarselo a este select que tengo :

SELECT XMLTABLE.XMLROW.value('(NumeroAutorizacion) [1]', 'varchar(100)' ) as 'NumeroAutorizacion', XMLTABLE.XMLROW.value('(Monto) [1]', 'varchar(100)' ) as 'Monto', XMLTABLE.XMLROW.value('(IdCuenta) [1]', 'varchar(100)' ) as 'IdCuenta' FROM @XML.nodes('/Request/NS1RegistraPagoSFBReq/Param') XMLTABLE(XMLROW)

Como hago para hacer un where, para pasarle el valor capturado en este caso

@numAutorizacion
Si mi respuesta te ha ayudado a resolver tus problemas, Selecciona "Proponer como respuesta"

Blog
J.Joaquin
lunes, 20 de julio de 2015 14:36

Responder

|

Citar

Inicie sesión para votar

Hombre, no nos haga trabajar a ciegas y ponga un ejemplo de la tabla, setencias INSERT y resultado esperado.

Aca un ejemplo:

DECLARE @T table (
TransactionID int,
Request xml
);

INSERT INTO @T (TransactionID, Request)
VALUES
    (34, '
<Request>
    <Action>RegistraPagoSFB</Action>
    <NS1:RegistraPagoSFBReq xmlns:NS1="xmlns">
        <Param>
            <NumeroAutorizacion>0620151629385165</NumeroAutorizacion>
            <Monto>804</Monto>
            <IdCuenta>100010130022373</IdCuenta>
            <Descripcion>TSS 06-2015</Descripcion>
            <Recibo>9292849</Recibo>
            <Mensaje>Factura Autorizada</Mensaje>
        </Param>
        <CONTROL>
            <APP_CODE>NetBankingEmpresas</APP_CODE>
        </CONTROL>
    </NS1:RegistraPagoSFBReq>
</Request>'),
    (35, '
<Request>
    <Action>RegistraPagoSFB</Action>
    <NS1:RegistraPagoSFBReq xmlns:NS1="xmlns">
        <Param>
            <NumeroAutorizacion>0620151629385166</NumeroAutorizacion>
            <Monto>800</Monto>
            <IdCuenta>100010130022376</IdCuenta>
            <Descripcion>TSS 06-2015</Descripcion>
            <Recibo>9292850</Recibo>
            <Mensaje>Factura Autorizada</Mensaje>
        </Param>
        <CONTROL>
            <APP_CODE>NetBankingEmpresas</APP_CODE>
        </CONTROL>
    </NS1:RegistraPagoSFBReq>
</Request>'),
(36, '
<Request>
    <Action>RegistraPagoSFB</Action>
    <NS1:RegistraPagoSFBReq xmlns:NS1="xmlns">
        <Param>
            <NumeroAutorizacion>0620151629385167</NumeroAutorizacion>
            <Monto>840</Monto>
            <IdCuenta>100010130022377</IdCuenta>
            <Descripcion>TSS 06-2015</Descripcion>
            <Recibo>9292851</Recibo>
            <Mensaje>Factura Autorizada</Mensaje>
        </Param>
        <CONTROL>
            <APP_CODE>NetBankingEmpresas</APP_CODE>
        </CONTROL>
    </NS1:RegistraPagoSFBReq>
</Request>');


-- todas las transacciones
WITH XMLNAMESPACES ('xmlns' AS NS1)
SELECT
    T.TransactionID,
    T.Request.value('(Request/NS1:RegistraPagoSFBReq/Param/NumeroAutorizacion/text())[1]', 'varchar(50)') AS NumeroAutorizacion
FROM
    @T AS T;
    
-- Autorizacion relacionada a una transaccion en especifico
DECLARE @TransactionID int = 34;

WITH XMLNAMESPACES ('xmlns' AS NS1)
SELECT
    T.TransactionID,
    N.x.value('(text())[1]', 'varchar(50)') AS NumeroAutorizacion
FROM
    @T AS T
    CROSS APPLY
    T.Request.nodes('(Request/NS1:RegistraPagoSFBReq/Param/NumeroAutorizacion)[1]') AS N(x)
WHERE
    T.TransactionID = @TransactionID;

-- Buscar un Autorizacion en especifico
DECLARE @NumeroAutorizacion varchar(50) = '0620151629385167';

WITH XMLNAMESPACES ('xmlns' AS NS1)
SELECT
    T.TransactionID,
    R.NumeroAutorizacion
FROM
    @T AS T
    CROSS APPLY
    (
    SELECT 
        T.Request.value('(Request/NS1:RegistraPagoSFBReq/Param/NumeroAutorizacion/text())[1]', 'varchar(50)') AS NumeroAutorizacion
    ) AS R
WHERE
    R.NumeroAutorizacion = @NumeroAutorizacion;

-- Buscar un Autorizacion en especifico
-- usando xml metodo exist()
WITH XMLNAMESPACES ('xmlns' AS NS1)
SELECT
    T.TransactionID,
    T.Request.value('(Request/NS1:RegistraPagoSFBReq/Param/NumeroAutorizacion/text())[1]', 'varchar(50)') AS NumeroAutorizacion
FROM
    @T AS T
WHERE
    T.Request.exist('Request/NS1:RegistraPagoSFBReq/Param/NumeroAutorizacion[(text()[1]) = sql:variable("@NumeroAutorizacion")]') = 1;
GO

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Propuesto como respuesta Miguel Egea GómezMVP, Moderator lunes, 20 de julio de 2015 15:38
Marcado como respuesta Karen MalagónModerator martes, 21 de julio de 2015 18:07

lunes, 20 de julio de 2015 15:37

1

Inicie sesión para votar
te iba a poner algo así, pero la respuesta de AMB es mucho mas completa

ahora en lugar de nodes has de usar exists, no me atrevo a escribirte el código pero vamos la idea, es algo así

DECLARE @isbn varchar(20) SET @isbn = '0-7356-1588-2' SELECT xCol FROM T WHERE xCol.exist ('/book/@ISBN[. = sql:variable("@isbn")]') = 1

Creo que básicamente lo que buscabas es tanto exist, como sql:variable("@var")

para cerrar la ecuación

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA
lunes, 20 de julio de 2015 15:38

Responder

|

Citar

Moderador

Productos

Resources

Solutions

Actualizaciones

Pruebas

Sitios relacionados

Aprendizaje

Certificaciones

Otros recursos

Por producto

Otros vínculos

¿No es experto en TI?

Principales respuestas

Query campo xml utilizando sql server 2008

Pregunta

Respuestas

Todas las respuestas