none
Tratar registros XML como tabla en procedimientos almacenados RRS feed

  • Pregunta

  • Estoy tratando de insertar en SQL Server mediante un procedimiento almacenado registros XML, el problema es que lo que he encontrado es usando los atributos de las etiquetas y no los valores que esta tienen.

    A ver si consigo explicarme, yo quiero que cada registro de relaciones del siguiente XML me lo trate como tal, os muestro el código:

    <ListaRelaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>01</CodiIdentificador>
    		<ValorIdentificador>XXXXX1</ValorIdentificador>
    		<NumIdentificador>1</NumIdentificador>
    	</Relaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>01</CodiIdentificador>
    		<ValorIdentificador>XXXXX2</ValorIdentificador>
    		<NumIdentificador>2</NumIdentificador>
    	</Relaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>10</CodiIdentificador>
    		<ValorIdentificador>31/03/2017</ValorIdentificador>
    		<NumIdentificador>1</NumIdentificador>
    	</Relaciones>
    </ListaRelaciones>

    Es decir que si hiciera una select * from "Listarelaciones/Relaciones", me sacara un cursor con los datos de cada relación.

    NReferencia | CodiIdentificador | ValorIdentificador | NumIdentificador

    0 | 01 | XXXXX1 | 1

    0 | 01 | XXXXX2 | 2

    0 |10 | 31/03/2017 | 1

    En cambio en los ejemplos que estoy encontrando para manejar dichos datos, hay que crear un XML tal que así

    <ListaRelaciones>
    	<Relaciones NReferencia="0" CodiIdentificador="01" ValorIdentificador="XXXXX1" NumIdentificador="1">
    	<Relaciones NReferencia="0" CodiIdentificador="01" ValorIdentificador="XXXXX2" NumIdentificador="2">
    	<Relaciones NReferencia="0" CodiIdentificador="10" ValorIdentificador="31/03/2017" NumIdentificador="1">
    </ListaRelaciones>

    Me gustaría saber si alguien puede ayudarme a usar el formato del XML del primer ejemplo expuesto en SQL Server.

    miércoles, 29 de marzo de 2017 13:16

Respuestas

  • El fragmento que usas es centrado en elementos y no atributos.

    Trata:

    DECLARE @frag xml = N'<ListaRelaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>01</CodiIdentificador>
    		<ValorIdentificador>XXXXX1</ValorIdentificador>
    		<NumIdentificador>1</NumIdentificador>
    	</Relaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>01</CodiIdentificador>
    		<ValorIdentificador>XXXXX2</ValorIdentificador>
    		<NumIdentificador>2</NumIdentificador>
    	</Relaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>10</CodiIdentificador>
    		<ValorIdentificador>31/03/2017</ValorIdentificador>
    		<NumIdentificador>1</NumIdentificador>
    	</Relaciones>
    </ListaRelaciones>';
    
    SELECT
    	T.col1.value('(NReferencia/text())[1]', 'int') AS NReferencia,
    	T.col1.value('(CodiIdentificador/text())[1]', 'varchar(5)') AS CodiIdentificador,
    	T.col1.value('(ValorIdentificador/text())[1]', 'varchar(25)') AS ValorIdentificador,
    	T.col1.value('(NumIdentificador/text())[1]', 'int') AS NumIdentificador
    FROM
    	(VALUES (@frag)) AS F(col1)
    	CROSS APPLY
        F.col1.nodes('/ListaRelaciones/Relaciones') AS T(col1)
    ;
    GO

    Cambia el tipo de data (int, varchar, etc.) de cada elemento que se use en la lista de columnas de acuerdo a tu conveniencia.


    AMB

    Some guidelines for posting questions...

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

    miércoles, 29 de marzo de 2017 14:04
  • Hola Hunchback,

    Finalmente he encontrado una forma más sencilla sin necesidad de usar CROSS APLY, espero que le sirva a cualquier otro que la pueda necesitar.

    Adjunto el codigo:

    DECLARE @frag xml = N'<ListaRelaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>01</CodiIdentificador>
    		<ValorIdentificador>XXXXX1</ValorIdentificador>
    		<NumIdentificador>1</NumIdentificador>
    	</Relaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>01</CodiIdentificador>
    		<ValorIdentificador>XXXXX2</ValorIdentificador>
    		<NumIdentificador>2</NumIdentificador>
    	</Relaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>10</CodiIdentificador>
    		<ValorIdentificador>31/03/2017</ValorIdentificador>
    		<NumIdentificador>1</NumIdentificador>
    	</Relaciones>
    </ListaRelaciones>';
    
    SELECT
    	T.col1.query('NReferencia').value('.', 'int') AS NReferencia,
    	T.col1.query('CodiIdentificador').value('.', 'varchar(5)') AS CodiIdentificador,
    	T.col1.query('ValorIdentificador').value('.', 'varchar(25)') AS ValorIdentificador,
    	T.col1.query('NumIdentificador').value('.', 'int') AS NumIdentificador
    FROM
    	@frag.nodes('/ListaRelaciones/Relaciones') AS T(col1)
    ;
    GO

    Saludos

    miércoles, 29 de marzo de 2017 21:38

Todas las respuestas

  • El fragmento que usas es centrado en elementos y no atributos.

    Trata:

    DECLARE @frag xml = N'<ListaRelaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>01</CodiIdentificador>
    		<ValorIdentificador>XXXXX1</ValorIdentificador>
    		<NumIdentificador>1</NumIdentificador>
    	</Relaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>01</CodiIdentificador>
    		<ValorIdentificador>XXXXX2</ValorIdentificador>
    		<NumIdentificador>2</NumIdentificador>
    	</Relaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>10</CodiIdentificador>
    		<ValorIdentificador>31/03/2017</ValorIdentificador>
    		<NumIdentificador>1</NumIdentificador>
    	</Relaciones>
    </ListaRelaciones>';
    
    SELECT
    	T.col1.value('(NReferencia/text())[1]', 'int') AS NReferencia,
    	T.col1.value('(CodiIdentificador/text())[1]', 'varchar(5)') AS CodiIdentificador,
    	T.col1.value('(ValorIdentificador/text())[1]', 'varchar(25)') AS ValorIdentificador,
    	T.col1.value('(NumIdentificador/text())[1]', 'int') AS NumIdentificador
    FROM
    	(VALUES (@frag)) AS F(col1)
    	CROSS APPLY
        F.col1.nodes('/ListaRelaciones/Relaciones') AS T(col1)
    ;
    GO

    Cambia el tipo de data (int, varchar, etc.) de cada elemento que se use en la lista de columnas de acuerdo a tu conveniencia.


    AMB

    Some guidelines for posting questions...

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

    miércoles, 29 de marzo de 2017 14:04
  • Hola Hunchback,

    Tal vez es porque no se, pero ahora mismo con el codigo que me has puesto me da error en el

    (VALUES (@frag)) AS F(col1)
    	CROSS APPLY
        F.col1.nodes('/ListaRelaciones/Relaciones') AS T(col1)

    Se que la solución es usar el CROSS APPLY, así que estoy buscando más, pero gracias por tu colaboración.

    miércoles, 29 de marzo de 2017 15:51
  • Es que no mencionas la version de SQL Server que usas.

    Lo anterior indica que estas usando la version 2005 por lo que el constructor de tabla VALUES dara error ya que fue incluido en la version 2008.

    A cambio sustituye VALUES por una SELECT.

    (SELECT @frag AS col1) AS F


    AMB

    Some guidelines for posting questions...

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

    miércoles, 29 de marzo de 2017 15:59
  • Efectivamente, así era, por lo visto el problema que he tenido es que usamos diferentes versiones en los entornos de pruebas y de real.

    Muchisimas gracias.

    miércoles, 29 de marzo de 2017 16:10
  • Hola Hunchback,

    Finalmente he encontrado una forma más sencilla sin necesidad de usar CROSS APLY, espero que le sirva a cualquier otro que la pueda necesitar.

    Adjunto el codigo:

    DECLARE @frag xml = N'<ListaRelaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>01</CodiIdentificador>
    		<ValorIdentificador>XXXXX1</ValorIdentificador>
    		<NumIdentificador>1</NumIdentificador>
    	</Relaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>01</CodiIdentificador>
    		<ValorIdentificador>XXXXX2</ValorIdentificador>
    		<NumIdentificador>2</NumIdentificador>
    	</Relaciones>
    	<Relaciones>
    		<NReferencia>0</NReferencia>
    		<CodiIdentificador>10</CodiIdentificador>
    		<ValorIdentificador>31/03/2017</ValorIdentificador>
    		<NumIdentificador>1</NumIdentificador>
    	</Relaciones>
    </ListaRelaciones>';
    
    SELECT
    	T.col1.query('NReferencia').value('.', 'int') AS NReferencia,
    	T.col1.query('CodiIdentificador').value('.', 'varchar(5)') AS CodiIdentificador,
    	T.col1.query('ValorIdentificador').value('.', 'varchar(25)') AS ValorIdentificador,
    	T.col1.query('NumIdentificador').value('.', 'int') AS NumIdentificador
    FROM
    	@frag.nodes('/ListaRelaciones/Relaciones') AS T(col1)
    ;
    GO

    Saludos

    miércoles, 29 de marzo de 2017 21:38