tag - La mejor forma de insertar datos XML a granel en la base de datos de SQL Server 2005

update tag xml sql server

Question

Tengo que triturar entre 25 y 30 XML en mi base de datos de SQL Server 2005 (el tamaño total sería de alrededor de 10 MB). Y necesito que esta lógica se ejecute automáticamente tan pronto como se copien nuevos archivos xml en el servidor.

He leído muchas publicaciones en este sitio y también en otros sitios, pero todavía no puedo concluir sobre qué debo usar para triturar datos.

Por favor me deja saber qué opción debo ir con

1. Copia SqlBulk
2. Deserialización de C #
3. SSIS

Tengo que crear clases C # para mis modelos de datos. Entonces la deserialización de C # fue mi primera opción. Pero por favor déjenme saber qué opción será la correcta desde una perspectiva de rendimiento.

Otra cosa que olvidé mencionar es que la estructura de los archivos XML variará. No sería lo mismo. Tendré tablas que tendrán todas las columnas que posiblemente puedan ser pobladas. Pero los xmls no tendrán todos los datos en todo momento.

Muestra del xml

<?xml version="1.0" encoding="utf-8"?>
<estateList date="2012-08-06T12:17:05">
  <uniqueID>22XXln</uniqueID>
  <category name="Apartment" /> 
  <listingAgent>
     <name>DIW Office</name>  
     <telephone type="BH">96232 2345</telephone> 
     <telephone type="BH">9234 2399</telephone>
     <email>[email protected]</email>    
  </listingAgent>
  <inspectionTimes /> 
  <description>AVAILABLE NOW. </description> 
  <price>0</price>  
  <address display="yes">      
    <street>Lachlsan Street</street>        
    <ImagesContainer>        
       <img id="m" modTime="2012-08-06-12:17:05" url="http://images/2409802.jpg" format="jpg" /> 
       <img id="a" modTime="2012-08-06-12:17:05" /> 
    </ImagesContainer>     
  </address>
</estateList>

Gracias.

Answer 1

Dado que tiene su XML en una variable SQL, puede analizar fácilmente la mayoría de la información mediante T-SQL directo con el soporte de XQuery agregado en SQL Server 2005.

Pruebe algo como:

DECLARE @Input XML = ''<estateList date="2012-08-06T12:17:05">
  <uniqueID>22XXln</uniqueID>
  <category name="Apartment" /> 
  <listingAgent>
     <name>DIW Office</name>  
     <telephone type="BH">96232 2345</telephone> 
     <telephone type="BH">9234 2399</telephone>
     <email>[email protected]</email>    
  </listingAgent>
  <inspectionTimes /> 
  <description>AVAILABLE NOW. </description> 
  <price>0</price>  
  <address display="yes">      
    <street>Lachlsan Street</street>        
    <ImagesContainer>        
       <img id="m" modTime="2012-08-06-12:17:05" url="http://images/2409802.jpg" format="jpg" /> 
       <img id="a" modTime="2012-08-06-12:17:05" /> 
    </ImagesContainer>     
  </address>
</estateList>''

SELECT
    EstateListDate = EstL.value(''@date'', ''datetime''),
    UniqueID = EstL.value(''(uniqueID)[1]'', ''varchar(20)''),
    Category = EstL.value(''(category/@name)[1]'', ''varchar(20)''),
    ListingAgentName = EstL.value(''(listingAgent/name)[1]'', ''varchar(50)''),
    ListingAgentTel = EstL.value(''(listingAgent/telephone)[1]'', ''varchar(50)''),
    ListingAgentEMail = EstL.value(''(listingAgent/email)[1]'', ''varchar(250)''),
    [Description] = EstL.value(''(description)[1]'', ''varchar(250)''),
    Price = EstL.value(''(price)[1]'', ''decimal(14,2)''),
    DisplayAddress = EstL.value(''(address/@display)[1]'', ''varchar(10)''),
    AddressStreet = EstL.value(''(address/street)[1]'', ''varchar(100)'')
FROM @input.nodes(''/estateList'') AS Tbl(EstL)

y deberías obtener:

Esta información podría insertarse fácilmente en una tabla. Y esta consulta podría ejecutarse contra cualquier cantidad de archivos XML en el disco, utilizando un paquete SSIS bastante sencillo (enumerar el XML, cargar cada uno en una variable de SQL, analizarlo, insertar datos en tablas, etc.)

PERO: la parte desafiante será preguntas como:

• ¿Puede haber más de un agente de listado? Y si es así: ¿cómo manejar eso?
• ¿Puede haber más de un número de teléfono y cómo lidiar con eso?
• qué hacer con las múltiples imágenes por dirección

Etcétera ....

Actualización: esta consulta aquí extraería la UniqueID y cada información completa de la etiqueta <img> de esa entrada XML y la mostraría (o la insertaría en otra tabla):

SELECT
    UniqueID = @input.value(''(/estateList/uniqueID)[1]'', ''varchar(20)''),
    ImageID = Images.value(''(img/@id)[1]'', ''varchar(20)''),
    ImageModTime = Images.value(''(img/@modTime)[1]'', ''varchar(50)''),
    ImageFormat = Images.value(''(img/@format)[1]'', ''varchar(20)''),
    ImageURL = Images.value(''(img/@url)[1]'', ''varchar(250)'')
FROM 
    @input.nodes(''/estateList/address/ImagesContainer'') AS Tbl(Images)