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