none
from XML to TABLE and return to XML RRS feed

  • Pregunta

  • Buenos días, 

    tengo un XML el cual lo quiero convertir a una tabla (SQL)

    Y Después de tenerlo en una tabla, con código sql la quiero pasar de nuevo al mismo formato XML

    cómo puedo lograrlo?

    <ROOT>
      <User Name="rgarza">
        <BrokerDesk Name="REMATE" />
        <Line LineaInicio="1" LineaFinal="10" />
            <Command>Grupo3</Command>
    	<Command>Grupo4</Command>
    	<Command>Grupo8</Command>
      </User>
    </ROOT>

    Aqui tengo un ejemplo, pero cuando ya lo tengo en tabla no la puedo regresar al mismo formato XML

    Ejemplo

    DECLARE @XML    XML
    SET @XML = CONVERT (XML,
    '
    <User name="rgarza">
    	<Default_value character="a" value="1" />
    	<Brokerdesk name="REMATE">
    		<Line startLine="1" endLine="10">
    			<Permission command="Grupo3" />
    			<Permission command="Grupo4" />
    			<Permission command="Grupo5" />
    		</Line>
    		<Line startLine="11" endLine="98">
    			<Permission command="Grupo1" />
    			<Permission command="Grupo2" />
    			<Permission command="Grupo13" />
    			<Permission command="Grupo14" />
    		</Line>
    	</Brokerdesk>
    	<Brokerdesk name="VAR">
    		<Line startLine="5" endLine="15">
    			<Permission command="00000010" />
    		</Line>
    		<Line startLine="20" endLine="100">
    			<Permission command="10000010" />
    			<Permission command="20000010" />
    		</Line>
    	</Brokerdesk>
    </User>
    ')
    select	t.c.value('@name[1]', 'varchar(20)') as UserName
    		,t1.c1.value('@name[1]', 'varchar(20)') as Brokerdesk		
    		,t2.c2.value('@startLine[1]', 'varchar(20)') as StartLine
    		,t2.c2.value('@endLine[1]', 'varchar(20)') as EndLine
    		,t3.c3.value('@command[1]', 'varchar(20)') as Command
    INTO #xml
    from @XML.nodes('//User') as t(c)
    cross apply t.c.nodes('Brokerdesk') as t1(c1)
    cross apply t1.c1.nodes('Line') as t2(c2)
    cross apply t2.c2.nodes('Permission') as t3(c3)
    
    
    SELECT * FROM #xml
    
    
    
    
    SELECT 
    		A.UserName		as [@Name],
    		A.Brokerdesk	as [BrokerDesk/@Name],
    		A.StartLine		as [Line/@LineaInicio],
    		A.EndLine		as [Line/@LineaFinal],
    		--CA.Command,
    		A.Command 
    FROM #xml as a
    --INNER JOIN (select userName, Brokerdesk, StartLine, EndLine, Command from #xml) as b ON a.StartLine = b.StartLine and a.EndLine = b.EndLine and a.UserName = b.UserName and a.Brokerdesk = b.Brokerdesk
    --cross apply (select userName, Brokerdesk, StartLine, EndLine, Command from #xml as b where a.StartLine = b.StartLine and a.EndLine = b.EndLine and a.Brokerdesk = b.Brokerdesk and a.UserName = b.UserName) as CA
    FOR XML PATH ('User'), ROOT ('ROOT')
    
    
    
    
    DROP TABLE #xml


    saludos

    jueves, 25 de junio de 2015 16:00

Respuestas

  • Trata:

    DECLARE @XML xml = '
    <ROOT>
    	<User Name="rgarza">
    		<BrokerDesk Name="REMATE" />
    		<Line LineaInicio="1" LineaFinal="10" />
    		<Command>Grupo3</Command>
    		<Command>Grupo4</Command>
    		<Command>Grupo8</Command>
    	</User>
    </ROOT>';
    
    SELECT
        t.c.value('@Name[1]', 'varchar(20)') as UserName
        ,t1.c1.value('@Name[1]', 'varchar(20)') as Brokerdesk		
        ,t2.c2.value('@LineaInicio[1]', 'varchar(20)') as StartLine
        ,t2.c2.value('@LineaFinal[1]', 'varchar(20)') as EndLine
        ,t3.c3.value('text()[1]', 'varchar(20)') as Command
    INTO
        #xml
    FROM
        @XML.nodes('ROOT/User') as t(c)
        cross apply t.c.nodes('BrokerDesk') as t1(c1)
        cross apply t.c.nodes('Line') as t2(c2)
        cross apply t.c.nodes('Command') as t3(c3);
    GO
    WITH U AS (
    SELECT DISTINCT
    	UserName
    FROM
    	#xml
    )
    , B AS (
    SELECT DISTINCT
        UserName,
    	Brokerdesk
    FROM
    	#xml
    )
    , L AS (
    SELECT DISTINCT
    	UserName,
    	Brokerdesk,
    	StartLine,
    	EndLine
    FROM
    	#xml
    )
    SELECT
        U.UserName AS [@Name],
        (
        SELECT
        	B.BrokerDesk AS [@Name]
        FROM
        	B
        WHERE
            B.UserName = U.UserName
        FOR XML PATH('BrokerDesk'), TYPE
        ),
    	(
    	SELECT
    		L.StartLine AS [@LineaInicio],
    		L.EndLine AS [@LineaFinal]
    	FROM
    		L
    	WHERE
    	    L.UserName = U.UserName
    	FOR XML PATH('Line'), TYPE
    	),
        (
        SELECT
            P.Command AS [text()]
        FROM
            #xml AS P
        WHERE
            P.UserName = U.UserName
        FOR XML PATH('Command'), TYPE
        )
    FROM
        U
    FOR XML PATH ('User'), ROOT ('ROOT'), TYPE;
    GO
    DROP TABLE #xml;
    GO
    



    AMB

    Some guidelines for posting questions...

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


    • Editado HunchbackMVP viernes, 26 de junio de 2015 20:53
    • Marcado como respuesta kakaroto2012 lunes, 29 de junio de 2015 11:54
    viernes, 26 de junio de 2015 20:46
  • La clave del query que acabas de hacer es el:  TEXT()

    Tu query es correcto, muchas gracias por tu tiempo y esfuerzo, ahora te muestro como me quedó

    en un código más compacto.

    <ROOT>
    	<User Name="rgarza">
    		<BrokerDesk Name="REMATE" />
    		<Line LineaInicio="1" LineaFinal="10" />
    		<Command>Grupo3</Command>
    		<Command>Grupo4</Command>
    		<Command>Grupo8</Command>
    	</User>
    </ROOT>

    SELECT	t.c.value('@Name[1]','varchar(20)') as UserName,
    		t1.c1.value('@Name[1]','varchar(20)') as BrokerDesk,
    		t2.c2.value ('@LineaInicio','int') as LineaInicial,
    		t2.c2.value ('@LineaFinal','int') as LineaFinal,
    		t3.c3.value('text()[1]','varchar(20)') as Command
    FROM @XML.nodes('//User') as t(c)
    cross apply t.c.nodes('BrokerDesk') as t1(c1)
    cross apply t.c.nodes('Line') as t2(c2)
    cross apply t.c.nodes('Command') as t3(c3)

    Buen inicio de semana y de nuevo muchas gracias por tu valioso tiempo y valioso conocimiento.


    saludos

    • Marcado como respuesta kakaroto2012 lunes, 29 de junio de 2015 11:54
    lunes, 29 de junio de 2015 11:53

Todas las respuestas

  • Se hace dificil porque existe relacion uno a muchos en el documento pero lo hicistes una sola tabla.

    Trata:

    WITH U AS (
    SELECT DISTINCT
    	UserName
    FROM
    	#xml
    )
    , B AS (
    SELECT DISTINCT
        UserName,
    	Brokerdesk
    FROM
    	#xml
    )
    , L AS (
    SELECT DISTINCT
    	UserName,
    	Brokerdesk,
    	StartLine,
    	EndLine
    FROM
    	#xml
    )
    SELECT
        U.UserName AS [@Name],
        (
        SELECT
        	B.BrokerDesk AS [@name],
        	(
        	SELECT
        		L.StartLine AS [@startLine],
        		L.EndLine AS [@endLine],
        		(
        		SELECT
        			P.Command AS [@command]
        		FROM
        			#xml AS P
        		WHERE
        		    P.UserName = U.UserName
        		    AND P.Brokerdesk = B.Brokerdesk
        		    AND P.StartLine = L.StartLine
        		    AND P.EndLine = L.EndLine
        		FOR XML PATH('Permission'), TYPE
        		)
        	FROM
        		L
        	WHERE
        	    L.UserName = U.UserName
        	    AND L.Brokerdesk = B.Brokerdesk
        	FOR XML PATH('Line'), TYPE
        	)
        FROM
        	B
        WHERE
            B.UserName = U.UserName
        FOR XML PATH('BrokerDesk'), TYPE
        )
    FROM
        U
    FOR XML PATH ('User'), ROOT ('ROOT'), TYPE;


    AMB

    Some guidelines for posting questions...

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

    jueves, 25 de junio de 2015 17:53
  • WOW!, lo hiciste perfectamente con el ejemplo que puse!

    lo regresaste tal cual era el original, muchas gracias por el tiempo que dedicaste a eso,!

    Solo me quedo con una duda, para el primer XML en el que debo trabajar, los valores no están en los atributos, si no que están como elementos, allí cómo puedo hacerle?



    saludos

    jueves, 25 de junio de 2015 18:03
  • <ROOT>
      <User Name="rgarza">
        <BrokerDesk Name="REMATE" />
        <Line LineaInicio="1" LineaFinal="10" />
            <Command>Grupo3</Command>
    	<Command>Grupo4</Command>
    	<Command>Grupo8</Command>
      </User>
    </ROOT>
    
    
    este xml debe ir a una table, y después desde la table regresarlo al mismo formato XML

    saludos

    jueves, 25 de junio de 2015 19:27
  • <ROOT>
    	<User Name="rgarza">
    		<BrokerDesk Name="REMATE" />
    		<Line LineaInicio="1" LineaFinal="10" />
    		<Command>Grupo3</Command>
    		<Command>Grupo4</Command>
    		<Command>Grupo8</Command>
    	</User>
    </ROOT>
    DECLARE @hdoc		INT
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML;
    
    SELECT UserName,BrokerDesk, InitialLine,FinalLine, Command
    FROM OPENXML(@hDoc,'ROOT/User')
    WITH
     (
       UserName		varchar(20) '@Name',			-- regresa un nodo (porque está en nodo Line)
       BrokerDesk	varchar(20) 'BrokerDesk/@Name',
       InitialLine	int			'Line/@LineaInicio',
       FinalLine	int			'Line/@LineaFinal',
       Command	varchar(20)	'.'
     )
    
     EXEC sp_xml_removedocument @hDoc
    

    con éste código no puedo obtener la última lista en filas, solo me sale en renglones

    quiero que la columna Command me salga en filas en lugar de un solo renglon

    supongo que debo añadir otro query, pero no sé como


    saludos

    viernes, 26 de junio de 2015 13:39
  • Trata:

    DECLARE @XML xml = '
    <ROOT>
    	<User Name="rgarza">
    		<BrokerDesk Name="REMATE" />
    		<Line LineaInicio="1" LineaFinal="10" />
    		<Command>Grupo3</Command>
    		<Command>Grupo4</Command>
    		<Command>Grupo8</Command>
    	</User>
    </ROOT>';
    
    SELECT
        t.c.value('@Name[1]', 'varchar(20)') as UserName
        ,t1.c1.value('@Name[1]', 'varchar(20)') as Brokerdesk		
        ,t2.c2.value('@LineaInicio[1]', 'varchar(20)') as StartLine
        ,t2.c2.value('@LineaFinal[1]', 'varchar(20)') as EndLine
        ,t3.c3.value('text()[1]', 'varchar(20)') as Command
    INTO
        #xml
    FROM
        @XML.nodes('ROOT/User') as t(c)
        cross apply t.c.nodes('BrokerDesk') as t1(c1)
        cross apply t.c.nodes('Line') as t2(c2)
        cross apply t.c.nodes('Command') as t3(c3);
    GO
    WITH U AS (
    SELECT DISTINCT
    	UserName
    FROM
    	#xml
    )
    , B AS (
    SELECT DISTINCT
        UserName,
    	Brokerdesk
    FROM
    	#xml
    )
    , L AS (
    SELECT DISTINCT
    	UserName,
    	Brokerdesk,
    	StartLine,
    	EndLine
    FROM
    	#xml
    )
    SELECT
        U.UserName AS [@Name],
        (
        SELECT
        	B.BrokerDesk AS [@Name]
        FROM
        	B
        WHERE
            B.UserName = U.UserName
        FOR XML PATH('BrokerDesk'), TYPE
        ),
    	(
    	SELECT
    		L.StartLine AS [@LineaInicio],
    		L.EndLine AS [@LineaFinal]
    	FROM
    		L
    	WHERE
    	    L.UserName = U.UserName
    	FOR XML PATH('Line'), TYPE
    	),
        (
        SELECT
            P.Command AS [text()]
        FROM
            #xml AS P
        WHERE
            P.UserName = U.UserName
        FOR XML PATH('Command'), TYPE
        )
    FROM
        U
    FOR XML PATH ('User'), ROOT ('ROOT'), TYPE;
    GO
    DROP TABLE #xml;
    GO
    



    AMB

    Some guidelines for posting questions...

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


    • Editado HunchbackMVP viernes, 26 de junio de 2015 20:53
    • Marcado como respuesta kakaroto2012 lunes, 29 de junio de 2015 11:54
    viernes, 26 de junio de 2015 20:46
  • La clave del query que acabas de hacer es el:  TEXT()

    Tu query es correcto, muchas gracias por tu tiempo y esfuerzo, ahora te muestro como me quedó

    en un código más compacto.

    <ROOT>
    	<User Name="rgarza">
    		<BrokerDesk Name="REMATE" />
    		<Line LineaInicio="1" LineaFinal="10" />
    		<Command>Grupo3</Command>
    		<Command>Grupo4</Command>
    		<Command>Grupo8</Command>
    	</User>
    </ROOT>

    SELECT	t.c.value('@Name[1]','varchar(20)') as UserName,
    		t1.c1.value('@Name[1]','varchar(20)') as BrokerDesk,
    		t2.c2.value ('@LineaInicio','int') as LineaInicial,
    		t2.c2.value ('@LineaFinal','int') as LineaFinal,
    		t3.c3.value('text()[1]','varchar(20)') as Command
    FROM @XML.nodes('//User') as t(c)
    cross apply t.c.nodes('BrokerDesk') as t1(c1)
    cross apply t.c.nodes('Line') as t2(c2)
    cross apply t.c.nodes('Command') as t3(c3)

    Buen inicio de semana y de nuevo muchas gracias por tu valioso tiempo y valioso conocimiento.


    saludos

    • Marcado como respuesta kakaroto2012 lunes, 29 de junio de 2015 11:54
    lunes, 29 de junio de 2015 11:53