XQuery e campi XML in SQL Server

Eccone una breve anticipazione.

Partiamo dai componenti base della ricetta:
-una tabella per Sql Server
    CREATE TABLE [dbo].[tabellaXML](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [testo] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [testoxml] [xml] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-un elenco di prodotti (in xml)
<?xml version="1.0" encoding="utf-16"?>
<prodotti xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <prodotto codice="product1">
  <nome xsi:type="xsd:string">nome del prodotto con codice product1</nome>
  <descrizione xsi:type="xsd:string">descrizione lunga lunga lunga...</descrizione>
  <prezzi>
   <prezzo listino="IT_EUR" main="true" xsi:type="xsd:decimal">25.2</prezzo>
   <prezzo listino="US_USD" xsi:type="xsd:decimal">30.15</prezzo>
  </prezzi>
  <altricodici>
   <codice>ABC123</codice>
   <codice>DEF456</codice>
   <codice>MNO789</codice>
  </altricodici>
  </prodotto>
  <prodotto codice="product2">
  <nome xsi:type="xsd:string">nome del prodotto con codice product2</nome>
  <descrizione xsi:type="xsd:string">descrizione lunga lunga lunga...per il product2</descrizione>
  <prezzi>
   <prezzo listino="IT_EUR" main="true" xsi:type="xsd:decimal">26.3</prezzo>
   <prezzo listino="US_USD" xsi:type="xsd:decimal">31.26</prezzo>
   <prezzo listino="UK_GBP" xsi:type="xsd:decimal">18.34</prezzo>
  </prezzi>
  <altricodici>
   <codice>321cba</codice>
   <codice>654fed</codice>
   <codice>987onm</codice>
  </altricodici>
  </prodotto>
</prodotti>

-un ulteriore elenco di prodotti (sempre in xml)
<?xml version="1.0" encoding="utf-16"?>
<prodotti xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <prodotto codice="product3">
  <nome xsi:type="xsd:string">product3: questo e' il prodotto</nome>
  <descrizione xsi:type="xsd:string">descrizione corta!</descrizione>
  <prezzi>
   <prezzo listino="IT_EUR" main="true" xsi:type="xsd:decimal">98.44</prezzo>
   <prezzo listino="UK_GBP" xsi:type="xsd:decimal">16.85</prezzo>
  </prezzi>
  <altricodici>
   <codice>KOOLEE</codice>
  </altricodici>
  </prodotto>
  <prodotto codice="product4">
  <nome xsi:type="xsd:string">product4: il prodotto e' product4</nome>
  <descrizione xsi:type="xsd:string">descrizione del prodtto product4 lunga lunga lunga...ed ancora lunga lunga lunga</descrizione>
  <prezzi>
   <prezzo listino="IT_EUR" main="true" xsi:type="xsd:decimal">0.01</prezzo>
   <prezzo listino="US_USD" xsi:type="xsd:decimal">0.02</prezzo>
   <prezzo listino="UK_GBP" xsi:type="xsd:decimal">0.03</prezzo>
  </prezzi>
  <altricodici>
   <codice>NOODEE</codice>
  </altricodici>
  </prodotto>
</prodotti>

-una semplice XQUERY
  SELECT    
     testoXML.exist('(//prodotto/prezzi/prezzo[@listino="UK_GBP"])') AS pexist
   , testoXML.query('(//prodotto/prezzi/prezzo[@listino="UK_GBP"])[1]/../..')  AS prodotto
   , testoXML.query('(//prodotto/prezzi/prezzo[@listino="UK_GBP"])[1]/parent::node()')  AS prezzi 
   , testoXML.query('(//prodotto/prezzi/prezzo[@listino="UK_GBP"])[1]')  AS prezzo
   , testoXML.value('(//prodotto/prezzi/prezzo[@listino="UK_GBP"])[1]', 'decimal(10, 2)')  AS prezzo_valore
  FROM  tabellaXML

dopo aver popolato la tabella con i 2 xml di esempio (una INSERT fa tutto)
si esegue la query ottenendo il seguente risultato 
 
 
ed ora veniamo alla spiegazione dei comandi
-  testoXML.exist('(//prodotto/prezzi/prezzo[@listino="UK_GBP"])')
   se 1, indica se che c'e' un prodotto che ha un prezzo del listino 'UK_GBP', altrimenti 0
-  testoXML.query('(//prodotto/prezzi/prezzo[@listino="UK_GBP"])[1]/../..')
   recupera un prodotto, se questi ha un prezzo del listino 'UK_GBP' (risultato in xml)
-  testoXML.query('(//prodotto/prezzi/prezzo[@listino="UK_GBP"])[1]/parent::node()')
   recupera tutti i prezzi di un prodotto, se questi ha un prezzo del listino 'UK_GBP' (risultato in xml)
-  testoXML.value('(//prodotto/prezzi/prezzo[@listino="UK_GBP"])[1]', 'decimal(18, 0)')
   recupera il valore del prezzo del listino 'UK_GBP'

l'esempio qui proposto e' volutamente semplice ma immaginate, per un momento, che questa lista di prodotti sia in realta' un ordine completo di tutto (cliente, spedizione, prodotti, promozioni, etc),
quante e quali sono le possibilita' di questo strumento?
per esempio, e' piu' facile sapere:
-ammontare degli ordini secondo le promozioni
-totale degli ordini di un cliente in un range di tempo
-ammontare dei prodotti ordinati in un certo listino
-un report

ora tocca a voi!

strumenti utilizzati
-SQL Server 2005 Express
-Notepad2

Commenti