Ricerca in Campi XML

Immaginate un magazzino di spedizionieri che necessita di una descrizione dettagliata di ogni prodotto conservato al suo interno.
Nei suoi scaffali si protrebbero trovare prodotti come Sci, Televisioni, Automobili, Pc etc.... Come fare allora per gestire anche nel DB
dei dati così differenti tra loro per struttura ?
Utilizzando il tipo di dato XML....
Dall'edizione SQL Server 2005 è possibile utilizzare il tipo di dato XML all'interno dei nostri DB.
E' molto utile ma allo stesso tempo è assolutamente "vietato" abusarne.
Personalmente lo reputo indispensabile per salvare all'interno di un DB dei dati non strutturati, ovvero dei dati non riconducibili
ad una struttura sempre dello stesso tipo.

Vediamo come risolvere il problema precedentemente descritto....

Il primo passo è creare una tabella con un tipo di dato XML e con una PK clustered (vedremo dopo il motivo)

USE tempdb
GO

Create table TabCampoXML
(
 id int identity (1,1) PRIMARY KEY CLUSTERED ,
 valore xml
)

Una volta creata la tabella è necessario creare, se pensiamo di effettuare delle ricerche dettagliate sul campo XML,
degli indici. Per creare l'indice XML Primario è indispensabile che la tabella sia provvista di una PK Clustered.
L'indice XML primario è la struttura su cui si appoggiano le strutture degli altri indici che eventualmente andremo a creare.
Possimo creare indici su Proprietà, su Path e per singolo valore all'interno della colonna XML. Grazie a queste tipologie
di indici possiamo effettuare molte ricerche e particolarmente dettagliate all'interno dell'XML.

Creo gli indici

--Indice XML Primario se non lo creo non posso creare i 
--secondari specifici per Prpoerty, path e valu
CREATE PRIMARY XML INDEX XML_IX_TabCampoXML ON dbo.TabCampoXML(valore)
  WITH( STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE XML INDEX XML_IX_TabCampoXML_1 ON dbo.TabCampoXML(valore)
 USING XML INDEX XML_IX_TabCampoXML
 FOR PROPERTY
  WITH( STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE XML INDEX XML_IX_TabCampoXML_2 ON dbo.TabCampoXML(valore)
 USING XML INDEX XML_IX_TabCampoXML
 FOR PATH
  WITH( STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE XML INDEX XML_IX_TabCampoXML_3 ON dbo.TabCampoXML(valore)
 USING XML INDEX XML_IX_TabCampoXML
 FOR VALUE
  WITH( STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

A questo punto possiamo popolare la tabella. Per inserire dei dati possiamo usare una Insert into normalissima,
l'importante è che l'XML sia Well formed. In caso contrario l'insert fallisce.

Insert into TabCampoXML values
('
  Head
  GS M
  188
  950.00
'),
('
  Alfa Romeo
  159
  2.0
  170
  Diesel
'),
('
  Samsung
  Plasma
  42
')

Come vedete inserisco dati strutturati in modo sempre diverso.....

Vediamo di effettuare una ricerca :

/* La parte relativa al campo XML è CASE SENTIVE */

--Cerco eventuali Prodotti con la proporità "Lunghezza" = 188
Select [XML].valore from TabCampoXML as [XML] 
where [XML].valore.exist('(/Prodotto/Lunghezza)[.="188"]') = 1

--Cerco eventuali prodotti di Marca = Alfa romeo
Select [XML].valore from TabCampoXML as [XML] 
where [XML].valore.exist('//Marca[.="Alfa Romeo"]') = 1

--Cerco eventuali prodotti con cilindrata = 2.0
Select [XML].valore from TabCampoXML as [XML] 
where [XML].valore.value('(/Automobile/Cilindrata)[1]', 'varchar(25)') = '2.0'

--Cerco eventuali prodotti con Tipo = Plasma e restituisco al chiamante solo la Mara
Select [XML].valore.value('(/Televisione/Marca)[1]', 'varchar(25)') from TabCampoXML as [XML] 
where [XML].valore.value('(/Televisione/Tipo)[1]', 'varchar(25)') = 'plasma'

Ovviamente sarebbe opportuno associare alla ricerca negli indici XML anche una ricerca nella primary key della tabella, altrimenti SQL server sarà costretto ad implementare una clustered index scan sulla tabella al fine di trovare i dati da voi cercati nella colonna XML.

Qui trovate una guida sull'utilizzo degli indici XML.

Ciao