leer - En SQL Server, ¿puedo insertar múltiples nodos en XML desde una tabla?

xml sql server 2008

Question

Quiero generar algo de XML en un procedimiento almacenado basado en datos en una tabla.

La siguiente inserción me permite agregar muchos nodos pero tienen que estar codificados o usar variables (sql: variable):

SET @MyXml.modify(''
      insert
         <myNode>
            {sql:variable("@MyVariable")}
         </myNode>
      into (/root[1]) '') 

Así que podría recorrer cada registro en mi tabla, poner los valores que necesito en las variables y ejecutar la declaración anterior.

Pero, ¿hay alguna forma de que pueda hacer esto simplemente combinándolo con una declaración seleccionada y evitando el ciclo?

Editar He utilizado SELECT FOR XML para hacer cosas similares anteriormente, pero siempre me resulta difícil de leer cuando trabajo con una jerarquía de datos de múltiples tablas. Esperaba que hubiera algo utilizando la modify donde el XML generado es más explícito y más controlable.

Answer 1

¿Puedes decirnos algo más sobre lo que estás planeando hacer exactamente? ¿Simplemente genera datos XML basados ​​en un contenido de la tabla o agrega algunos datos de la tabla a una estructura xml existente?

Hay una gran serie de artículos sobre el tema en XML en SQLServer escritos por Jacob Sebastian, comienza con los conceptos básicos de generación de XML a partir de los datos en la tabla

Answer 2

¿Has intentado anidar las funciones con valor escalar FOR XML PATH? Con la técnica de anidación, puede frenar su SQL en piezas elementales muy manejables / legibles

Descargo de responsabilidad: el siguiente, aunque adaptado de un ejemplo de trabajo, no se ha probado literalmente

Algunos enlaces de referencia para la audiencia general

• http://msdn2.microsoft.com/en-us/library/ms178107(SQL.90).aspx
• http://msdn2.microsoft.com/en-us/library/ms189885(SQL.90).aspx

El ejemplo de nodo anidado más simple y de nivel más bajo

Considera la siguiente invocación

DECLARE  @NestedInput_SpecificDogNameId int
SET @NestedInput_SpecificDogNameId = 99
SELECT [dbo].[udfGetLowestLevelNestedNode_SpecificDogName] 
(@NestedInput_SpecificDogNameId)

Digamos que udfGetLowestLevelNestedNode_SpecificDogName se había escrito sin la cláusula FOR XML PATH, y para @NestedInput_SpecificDogName = 99 devuelve el único registro de conjunto de filas:

@SpecificDogNameId DogName 99 Astro

Pero con la cláusula FOR XML PATH,

CREATE FUNCTION dbo.udfGetLowestLevelNestedNode_SpecificDogName
(
@NestedInput_SpecificDogNameId
)
    RETURNS XML
    AS
    BEGIN

        -- Declare the return variable here
        DECLARE @ResultVar XML

        -- Add the T-SQL statements to compute the return value here
        SET @ResultVar =
            (
            SELECT 
                  @SpecificDogNameId as "@SpecificDogNameId",
                  t.DogName 
            FROM tblDogs t
            FOR XML PATH(''Dog'')
            )

        -- Return the result of the function
        RETURN @ResultVar

END

la función definida por el usuario produce el siguiente XML (los signos @ hacen que el campo SpecificDogNameId se devuelva como un atributo)

<Dog SpecificDogNameId=99>Astro</Dog>

Anidamiento de funciones definidas por el usuario de tipo XML

Las funciones definidas por el usuario, como el anterior udfGetLowestLevelNestedNode_SpecificDogName, se pueden anidar para proporcionar un método potente para producir XML complejo.

Por ejemplo, la función

CREATE FUNCTION [dbo].[udfGetDogCollectionNode]()
    RETURNS XML
    AS
    BEGIN

        -- Declare the return variable here
        DECLARE @ResultVar XML

        -- Add the T-SQL statements to compute the return value here
        SET @ResultVar =
            (
                SELECT  
                [dbo].[udfGetLowestLevelNestedNode_SpecificDogName]
                        (t.SpecificDogNameId)
                FROM tblDogs t

                FOR XML PATH(''DogCollection'') ELEMENTS
            )
        -- Return the result of the function
        RETURN @ResultVar

END

cuando se invoca como

SELECT [dbo].[udfGetDogCollectionNode]()

podría producir el nodo XML complejo (dados los datos subyacentes apropiados)

<DogCollection>
    <Dog SpecificDogNameId="88">Dino</Dog>
    <Dog SpecificDogNameId="99">Astro</Dog>
</DogCollection>

Desde aquí, puede seguir trabajando hacia arriba en el árbol anidado para crear una estructura XML tan compleja como desee.

CREATE FUNCTION [dbo].[udfGetAnimalCollectionNode]()
RETURNS XML
AS
BEGIN

DECLARE @ResultVar XML

SET @ResultVar =
(
SELECT 
dbo.udfGetDogCollectionNode(),
dbo.udfGetCatCollectionNode()
FOR XML PATH(''AnimalCollection''), ELEMENTS XSINIL
)

RETURN @ResultVar

END

cuando se invoca como

SELECT [dbo].[udfGetAnimalCollectionNode]()

el udf podría producir el nodo XML más complejo (dados los datos subyacentes apropiados)

<AnimalCollection>
  <DogCollection>
    <Dog SpecificDogNameId="88">Dino</Dog>
    <Dog SpecificDogNameId="99">Astro</Dog>
  </DogCollection>
  <CatCollection>
    <Cat SpecificCatNameId="11">Sylvester</Cat>
    <Cat SpecificCatNameId="22">Tom</Cat>
    <Cat SpecificCatNameId="33">Felix</Cat>
  </CatCollection>
</AnimalCollection>

Answer 3

Use sql: column en lugar de sql: variable. Puede encontrar información detallada aquí: http://msdn.microsoft.com/en-us/library/ms191214.aspx