Como leer un archivo xml para luego procesarlo con OpenXML ??

Muchas veces queremos procesar un archivo XML directamente en SQL, el tema es cual es la operatoria, usando transact-sql, para introducir el contenido del archivo en una variable Xml para luego procesar dicha variable con openxml.

El siguiente script puede ser una interesante opción para lograr el objetivo, utilizando una tabla temporaria y llenándola con la ejecución del comando "type" de líneas de comando (xp_cmdshell) y luego recorriendo el contenido de dicha tabla llenando la variable tan deseada por nosotros.

He aquí el script:

DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
declare @idoc int
DROP TABLE #tempXML
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

SET @FileName = 'E:\Export\CheckoutReport.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
-- SELECT @y = 95
SET @x = 0
WHILE @x <> @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK = @x
END
PRINT @FileContents
SELECT @FileContents as FileContents

exec sp_xml_preparedocument @idoc OUTPUT, @FileContents
-- SELECT stmt using OPENXML rowset provider
SELECT *
--FROM OPENXML ('E:\Export\CheckoutReport.xml', '/CheckoutReport/PurchaseList/BuyerPurchase',2)
FROM OPENXML (@idoc, '/CheckoutReport/PurchaseList/BuyerPurchase',2)
WITH (
InvoiceNumber VARCHAR(50) 'TransactionInfo/InvoiceNumber',
ShipmentNumber VARCHAR(50) 'ShipmentList/Shipment/ShipmentNumber',
ShipNameTitle VARCHAR(50) 'ShipmentList/Shipment/ShipNameTitle',
LotTitle VARCHAR(50) 'ShipmentList/Shipment/Items/Item/LotTitle',
SKU VARCHAR(20) 'ShipmentList/Shipment/Items/Item/SKU',
SiteName VARCHAR(50) 'ShipmentList/Shipment/Items/Item/SiteName'
)
EXEC sp_xml_removedocument @idoc

--SELECT * FROM #tempXML

-- DROP TABLE #tempXML

Actualización 24-10-2009
Adicionalmente, si se desea ingresar datos a la tabla directamente desde un archivo, el siguiente script puede ser la solución:

INSERT INTO base..tabla
SELECT xCol
FROM (SELECT * FROM OPENROWSET (BULK 'C:\directorio\archivoxml.xml', SINGLE_BLOB) AS xCol) AS R(xCol)

http://msdn.microsoft.com/en-us/library/ms345117%28SQL.90%29.aspx



Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...