In queste ultime settimane ci è capitato di dover aggiornare qualche tabella in modo rapido partendo da un dataset restituito da un servizio (leggi ws) esterno.
Il servizio in questione ci ritornava un dataset di qualche MB e, dopo qualche test con l'aggiornamento riga per riga (leggere una riga, richiamare una stored procedure cui passavamo tutti i parametri, leggere un'altra riga...) abbiamo preferito (quasi per gioco) ricorrere all'OpenXML per fornire tutto il dataset ad una stored procedure e poi gestire da questa insert e update del caso.
La sp in questione accetta un parametro di tipo ntext (max 2GB, che mi sembrano abbastanza), prepara il documento (sp_xml_preparedocument), utilizza l'xml come fosse una tabella ed infine rimuove l'xml generato dalla memoria (sp_xml_removedocument).
Unico accorgimento: ripulire il dataset da eventuali namespace prima di passarlo alla stored, o prevedere tali namespace durante l'elaborazione con OpenXML.

Inutile dire che le prestazioni sono migliorate di almeno un ordine di grandezza: impieghiamo più tempo a passare il dataset alla sp che SQL Server 2000 (è questo il db) a processarlo.
I primi file erano dell'ordine di una decina di MB...
Quando ci è arrivata una sberla da 75 MB... siamo rimasti di stucco: General Network Error. Il pb non era di timeout, ma semplicemente non riuscivamo a passare i 75 MB alla stored.
Qualcuno ci hanno consigliato di utilizzare una bulk che, forse, non avrebbe dato l'errore di cui sopra perché il file se lo sarebbe andato a pescare direttamente Sql Server.
L'idea non è sciocca, ma noi abbiamo un dataset, dovremmo fare qualcosa per convertirlo in un file simil-csv (magari un xslt?) realizzare un file di mapping, poi lanciare la bulk (che fa una insert) e poi cmq lanciare una sp che faccia le opportune valutazioni del caso per aggiornare/inserire.
Non ci è piaciuta per diverese ragioni, prima fra tutte il troppo tempo necessario a processare un dataset per la conversione un formato "piatto".
Qualcuno ci aveva accennato ad una sorta di "XML Bulk Insert", e quindi, armati di Google e della solita pazienza, ci siamo fatti una bella navigata.

Risultato della ricerca: SQLXML Bulkload!
Ora che sappiamo cosa usare, come lo usiamo?

Dobbiamo usare le librerie SQLXML (3.0 per Sql Server 2000, 4.0 per Sql 2005).
Quindi dopo averle scaricate ed installata, referenziamo la libreria Microsoft SQLXML BulkLoad 3.0 Type Library (fra i COM).
Se non la trovaste, cercate il file xblkld3.dll o xblkld4.dll nel path C:\Program Files\Common Files\System\Ole DB.
A tal proposito, segnalo che in un articolo veniva consigliato di convertire tale libreria in una .Net Compliant utilizzando il tool tlbimp. Inoltre si indicava di aggiungere il componente fra i Component Service.
Noi ne abbiamo fatto a meno referenziando direttamente la libreria COM. Voi avreste fatto diversamente? Perché?

E veniamo al codice (fare riferimento alla documentazione):
SQLXMLBulkLoad4Class bl = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();

bl.ConnectionString = "...";

bl.ErrorLogFile = @"c:\tmp\log.txt"; //se vogliamo che eventuali errori vengano riportati in un file

bl.SchemaGen = true; //se vogliamo generare la tabella destinazione dallo schema

bl.SGDropTables = true //drop table se esiste (valido nel caso di SchemaGen = true)

bl.Execute(@"c:\tmp\schema.xml", @"c:\tmp\data.xml");


Per esempio si potrebbe creare la tabella seguente:
CREATE TABLE Cust(CustomerID int PRIMARY KEY, CompanyName varchar(20), City varchar(20))


Il file xml pseguente per i dati:
<ROOT>

<Customers>

<CustomerID>1111</CustomerID>

<CompanyName>Sean Chai</CompanyName>

<City>NY</City>

</Customers>

<Customers>

<CustomerID>1112</CustomerID>

<CompanyName>Tom Johnston</CompanyName>

<City>LA</City>

</Customers>

<Customers>

<CustomerID>1113</CustomerID>

<CompanyName>Institute of Art</CompanyName>

</Customers>

</ROOT>

Il file seguente per l'XSD:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="Customers" sql:relation="Cust" >

<xsd:complexType>

<xsd:sequence>

<xsd:element name="CustomerID" type="xsd:integer" />

<xsd:element name="CompanyName" type="xsd:string" />

<xsd:element name="City" type="xsd:string" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>


O il file seguente per l'XDR:
<?xml version="1.0" ?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"

xmlns:sql="urn:schemas-microsoft-com:xml-sql" >



<ElementType name="CustomerID" dt:type="int" />

<ElementType name="CompanyName" dt:type="string" />

<ElementType name="City" dt:type="string" />



<ElementType name="root" sql:is-constant="1">

<element type="Customers" />

</ElementType>



<ElementType name="Customers" sql:relation="Cust" >

<element type="CustomerID" sql:field="CustomerID" />

<element type="CompanyName" sql:field="CompanyName" />

<element type="City" sql:field="City" />



</ElementType>

</Schema>


Si noti come sia semplice il mapping in XDR:
- sql:relation="nome_tabella" per associare un nodo ad una tabella
- sql:field="nome_campo" per associare il nome del campo al nome del tag xml

Nel caso di XSD le cose si semplificano ulteriormente:
- sql:relation="nome_tabella" per associare un nodo ad una tabella
- nome dell'elemento = nome del campo della tabella

Un po' di acronimi:
XSD sta per Xml Schema Definition, uno standard W3C, etc etc...
XDR (io non lo conoscenvo) sta per Xml Data Reduced, e doveva rappresentare una via di mezzo fra DTD e XSD. Era tutto in salsa Microsoft e quindi è stato abbandonato (nella versione SQLXML 4.0 è deprecato) in favore del più standard XSD.

Una cosa interessante di SQLXML è che consente anche di importare (da un solo file xml) in diverse tabelle, anche relazionate, verificare foreign key e altri constraint...
E' possibile fornire frammenti di XML (in pratica, una serie di XML senza root).
Inoltre consente di generare una tabella dallo schema che gli viene fornito.
Unica nota dolente (per me): come specificargli nello schema che un varchar deve essere un lungo max 20 e non 1000? O che il decimal deve essere in una specifica precisione? Io non sono riuscito a trovare il metodo, ma ho risolto creando le tabelle di appoggio una volta sola, coi campi che e come voglio, impedendo il drop/create. Se aveste un modo migliore, vi prego di indicarlo.

Di seguito alcuni link da cui abbiamo scopiazzato alla bell'e meglio ;). Alcuni articoli sono indicati per SQL Server 2005, ma applicabili (noi lo abbiamo fatto) anche a SQL Server 2000.

Spero sia utile.
Voi cosa avreste fatto?

Technorati : ,