recuperar - xml a tabla sql server

Seleccionar nodos XML como filas

Question

Estoy seleccionando de una tabla que tiene una columna XML usando T-SQL. Me gustaría seleccionar un cierto tipo de nodo y crear una fila para cada uno.

Por ejemplo, supongamos que estoy seleccionando de una tabla de personas . Esta tabla tiene una columna XML para direcciones . El XML tiene un formato similar al siguiente:

<address>
  <street>Street 1</street>
  <city>City 1</city>
  <state>State 1</state>
  <zipcode>Zip Code 1</zipcode>
</address>
<address>
  <street>Street 2</street>
  <city>City 2</city>
  <state>State 2</state>
  <zipcode>Zip Code 2</zipcode>
</address>

¿Cómo puedo obtener resultados como este?

Nombre Ciudad Estado

Joe Baker Seattle WA

Joe Baker Tacoma WA

Fred Jones Vancouver BC

Answer 1

Si puede usarlo, la API apk es conveniente para XML:

var addresses = dataContext.People.Addresses
    .Elements("address")
        .Select(address => new { 
            street  = address.Element("street").Value, 
            city    = address.Element("city").Value, 
            state   = address.Element("state").Value, 
            zipcode = address.Element("zipcode").Value, 
        });

Answer 2

Así es como lo hago genéricamente:

Destruí el código fuente XML a través de una llamada como

DECLARE @xmlEntityList xml
SET @xmlEntityList =
''
<ArbitrarilyNamedXmlListElement>
              <ArbitrarilyNamedXmlItemElement><SomeVeryImportantInteger>1</SomeVeryImportantInteger></ArbitrarilyNamedXmlItemElement>
              <ArbitrarilyNamedXmlItemElement><SomeVeryImportantInteger>2</SomeVeryImportantInteger></ArbitrarilyNamedXmlItemElement>
              <ArbitrarilyNamedXmlItemElement><SomeVeryImportantInteger>3</SomeVeryImportantInteger></ArbitrarilyNamedXmlItemElement>
</ArbitrarilyNamedXmlListElement>
''

    DECLARE @tblEntityList  TABLE(
        SomeVeryImportantInteger    int
    )

    INSERT @tblEntityList(SomeVeryImportantInteger)
    SELECT 
        XmlItem.query(''//SomeVeryImportantInteger[1]'').value(''.'',''int'') as SomeVeryImportantInteger
    FROM
        [dbo].[tvfShredGetOneColumnedTableOfXmlItems] (@xmlEntityList)

mediante la utilización de la función de valor escalar

/* Example Inputs */
/*
DECLARE @xmlListFormat xml
SET     @xmlListFormat =
            ''
            <ArbitrarilyNamedXmlListElement>
              <ArbitrarilyNamedXmlItemElement>004421UB7</ArbitrarilyNamedXmlItemElement>
              <ArbitrarilyNamedXmlItemElement>59020UH24</ArbitrarilyNamedXmlItemElement>
              <ArbitrarilyNamedXmlItemElement>542514NA8</ArbitrarilyNamedXmlItemElement>
            </ArbitrarilyNamedXmlListElement>
            ''
declare @tblResults TABLE 
(
    XmlItem xml
)

*/

-- =============================================
-- Author:      6eorge Jetson
-- Create date: 01/02/3003
-- Description: Shreds a list of XML items conforming to
--              the expected generic @xmlListFormat
-- =============================================
CREATE FUNCTION [dbo].[tvfShredGetOneColumnedTableOfXmlItems]  
(
    -- Add the parameters for the function here
    @xmlListFormat xml
)
RETURNS 
@tblResults TABLE 
(
    -- Add the column definitions for the TABLE variable here
    XmlItem xml
)
AS
BEGIN

    -- Fill the table variable with the rows for your result set
    INSERT @tblResults
    SELECT
        tblShredded.colXmlItem.query(''.'')   as XmlItem
    FROM
         @xmlListFormat.nodes(''/child::*/child::*'') as tblShredded(colXmlItem)

    RETURN 
END

--SELECT * FROM @tblResults

Answer 3

Aquí está su solución:

/* TEST TABLE */
DECLARE @PEOPLE AS TABLE ([Name] VARCHAR(20),  [Address] XML )
INSERT INTO @PEOPLE SELECT 
    ''Joel'', 
    ''<address>
      <street>Street 1</street>
      <city>City 1</city>
      <state>State 1</state>
      <zipcode>Zip Code 1</zipcode>
    </address>
    <address>
      <street>Street 2</street>
      <city>City 2</city>
      <state>State 2</state>
      <zipcode>Zip Code 2</zipcode>
    </address>''
UNION ALL SELECT
    ''Kim'', 
    ''<address>
      <street>Street 3</street>
      <city>City 3</city>
      <state>State 3</state>
      <zipcode>Zip Code 3</zipcode>
    </address>''

SELECT * FROM @PEOPLE

-- BUILD XML
DECLARE @x XML
SELECT @x = 
( SELECT 
      [Name]
    , [Address].query(''
            for $a in //address
            return <address 
                street="{$a/street}" 
                city="{$a/city}" 
                state="{$a/state}" 
                zipcode="{$a/zipcode}" 
            />
        '') 
  FROM @PEOPLE AS people 
  FOR XML AUTO
) 

-- RESULTS
SELECT [Name]    = T.Item.value(''../@Name'', ''varchar(20)''),
       street    = T.Item.value(''@street'' , ''varchar(20)''),
       city      = T.Item.value(''@city''   , ''varchar(20)''),
       state     = T.Item.value(''@state''  , ''varchar(20)''),
       zipcode   = T.Item.value(''@zipcode'', ''varchar(20)'')
FROM   @x.nodes(''//people/address'') AS T(Item)

/* OUTPUT*/

Name | street   | city   | state   | zipcode
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Joel | Street 1 | City 1 | State 1 | Zip Code 1
Joel | Street 2 | City 2 | State 2 | Zip Code 2
Kim  | Street 3 | City 3 | State 3 | Zip Code 3

Answer 4

En caso de que esto sea útil para cualquier otra persona que busque una solución "genérica", creé un procedimiento CLR que puede tomar un fragmento Xml como el anterior y "triturarlo" en un conjunto de resultados tabular, sin que usted proporcione ninguna información adicional sobre los nombres o tipos de columnas, o personalizando su llamada de cualquier forma para el fragmento Xml dado:

http://architectshack.com/ClrXmlShredder.ashx

Por supuesto, hay algunas restricciones (el xml debe ser de naturaleza "tabular" como esta muestra, la primera fila debe contener todos los elementos / columnas que se admitirán, etc.) - pero espero que esté unos pasos por delante de lo que está disponible incorporado.

Answer 5

Aquí hay una solución alternativa:

;with cte as 
(
    select id, name, addresses, addresses.value(''count(/address/city)'',''int'') cnt
    from @demo
)
, cte2 as
(
    select id, name, addresses, addresses.value(''((/address/city)[sql:column("cnt")])[1]'',''nvarchar(256)'') city, cnt-1 idx 
    from cte 
    where cnt > 0

    union all

    select cte.id, cte.name, cte.addresses, cte.addresses.value(''((/address/city)[sql:column("cte2.idx")])[1]'',''nvarchar(256)''), cte2.idx-1 
    from cte2 
    inner join cte on cte.id = cte2.id and cte2.idx > 0
)
select id, name, city 
from cte2 
order by id, city

FYI: He publicado otra versión de este SQL en el sitio de revisión de código aquí: https://codereview.stackexchange.com/questions/108805/select-field-in-an-xml-column-where-both-xml- and-table-contains-multiple-matches