Gestire documenti XML (Parte I)
L' introduzione del supporto alla struttura dati XMLintrodotta dall' SQL Server 2005, ha rappresentato forsela principale novità di quel prodotto.Nelle precedentiversioni infatti scrivere e leggere dati in un file XML erapossibile ma molto macchinoso; ora invece è possibileimmagazzinare i dati contenuti nell' XML direttamente inun database SQL Server. In questo tutorial vedremo comegestire questi documenti XML.
Le migliorie importate da SQL Server 2005 in riguardo agliXML consistono in:
- Supporto al tipo di dati XML
- Possibilità di specificare una XQuery da eseguire su un XML suddiviso in colonne
- Supporto al caricamento di grandi masse di dati in XML
- Migliorate le funzioni FOR XML ed OPENXML già presentate nell' SQL Server 2000
Vediamo ora un esempio, che dimostrerà come siapossibile creare una tabella che contenga una colonna XML,inserendovi inoltre alcuni records:
- Creiamo una tabella con una colonna XML
Create table dbo.Student(StudentID int,StudentName varchar(50),Studentcontactdetails XML)
- Inseriamo dati in XML validi nella tabella
Insert into dbo.Student values(1,'ABC','<ROOT><Student>123,XYZ street,London</Student></ROOT>')
Nel caso avessimo inserito datiinvalidi, avremmo ricevuto un errore; eccone unesempio:
Insert into dbo.Student values(1,'ABC','<ROOT><Student>123,XYZ street,London')Msg 9400, Level 16, State 1, Line 1XML parsing: line 1, character 36, unexpected end of input
Abbiamo anche la possibilità didichiarare variabili XML, con il codice
Declare @x xml
Typed vs UntypedXML
Untyped XML può essere inseritain ogni forma.Quando l' utente inserisce un valore nellacolonna XML, bisogna controllare che ciò chestà per essere inserito si attenga agli standardsdell' XML
Typed XML è usata quando l'utente desidera convalidare l' XML con un XML Schema.Quest' ultimo va posto e catalogato nel database; inquesto modo l' XML validato secondo un XML Schemarisulterà essere un Typed XML, dichiaratocosì:
Declare @x XML(schema.xmlschemacollection)
Un XML Schema è definibile invece così:
CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression
con:
- relational_schema: definisce il nome relativo allo schema
- sql_identifier: è l' identificatore SQL dello schema XML
- expression: è una variabile string che può essere costante o scalare
Esempio:
CREATE XML SCHEMA COLLECTION Chemicals AS '<schema xmlns="http://www.w3.org/2001/XMLSchema"><element name="root"><complexType><sequence><element name="ChemicalName" type="string"/><element name="Symbol" type="string"/></sequence></complexType></element></schema>'
Ora che l' XML Schema èstato catalogato, possiamo ottenervi informazioni in riguardousando la funzione "XML_schema_namespace" , comenell' esempio:
SELECT xml_schema_namespace(N'dbo',N'Chemicals')
Definito l' HMX Schema, possiamousarlo per i CREATE TABLE o per il DECLARE XML. Ecco unesempio:
1)
Declare @xml xml(Chemicals)set @xml='<root><ChemicalName>Sulphuric Acid</ChemicalName><Symbol>H2SO4</Symbol></root>'
2)
Insert into students (studentname,Labid,Experimentid,Experimentchemical)values ('ABC',1,1,'<root><ChemicalName>Sulphuric Acid</ChemicalName><Symbol>H2SO4</Symbol></root>') Richiedere i datiXML
Per ricavare i dati e la tipologiadati da un XML esistono diverse possibilità:
1)Metodo delle query:così si ricava un frammento di untyped XML. Con l'esempio seguente vediamo come ricavare un valore da unacolonna XML:
select experimentchemical.query('/root/ChemicalName') from students Risultato:
<ChemicalName>Sulphuric Acid</ChemicalName>
2)Metodo value: è simileal metodo delle query, ma accetta anche un parametroaggiuntivo per determinare il risultante datatypescalare.
3) Metodo di esistenza: accetta come input un'espressione, che seleziona un nodo singolo dentro ildocumento XML e risponde con True(1) se il nodo esiste, o conFalse(0) se non esiste.
Esempio:
select Experimentchemical.exist('/root/ChemicalName') from students 4) Metodo di modifica:può essere usato per manipolare i dati XML posti inuna tabella. Consiste in tre sotto-dichiarazioni:
- INSERT
- DELETE
- REPLACE
Esempio:
1)
declare @x xmldeclare @custid intset @x='<root><CustomerDescription CustID="101" CustomerName="ABCL Industries Limited"><Phonenumber><Work>1234567890</Work><Residence>1434546678</Residence></Phonenumber></CustomerDescription><CustomerDescription CustID="102" CustomerName="HAL Industries Limited"><Phonenumber><Work>1234567890</Work><Residence>1434546678</Residence></Phonenumber></CustomerDescription></root>'select @xset @x.modify('delete /root/CustomerDescription/@CustID')select @xResult:<root><CustomerDescription CustomerName="ABCL Industries Limited"><Phonenumber><Work>1234567890</Work><Residence>1434546678</Residence></Phonenumber></CustomerDescription><CustomerDescription CustomerName="HAL Industries Limited"><Phonenumber><Work>1234567890</Work><Residence>1434546678</Residence></Phonenumber></CustomerDescription></root>
2)
Per inserire un nuovo nodo o frammento XML, possiamo usarel' INSERT, con questa sintassi:
insertExpression1 ({as first | as last} into | after | beforeExpression2) Analizziamone gli argomenti:
Expression1: identifica uno o più nodi dainserire
into: i nodi identificati da Expression1 vengonoinseriti come diretti discendenti del nodo identificato inExpression2. Se quest' ultimo ha già uno opiù child nodes, dovremo decidere noi in quale puntoinserirlo; per porlo subito dopo il nodo identificato daExpression2 useremo
after
per porlo subito dopo Expression2useremo invece
before
per porlo congiunto al nodo definitoin Expression2, useremo
Expression2
Ecco un esempio:
USE AdventureWorks;GODECLARE @myDoc xmlSET @myDoc = '<Root><ProductDescription ProductID="1" ProductName="Road Bike"><Features></Features></ProductDescription> </Root>'SELECT @myDoc-- insert first feature child (no need to specify as first or as last)SET @myDoc.modify('insert <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>into (/Root/ProductDescription/Features)[1]')SELECT @myDoc Come output riceveremo:
<Root><ProductDescription ProductID="1" ProductName="Road Bike"><Features><Maintenance>3 year parts and labor extended maintenance is available</Maintenance></Features></ProductDescription></Root>
5) Metodo dei nodi: può essere usato per estrarre datida un documento XML e usarli per generare sottonodisfruttabili per diversi scopi, come creare nuovi contenuti oinserirne di già pronti nelle tabelle. Ecco unesempio:
declare @x xmlset @x='<Root><row id="1"><AirportCode>MUM</AirportCode><AirportName>Mumbai</AirportName></row><row id="2"><AirportCode>MAS</AirportCode><AirportName>Madras</AirportName></row><row id="3"></row></Root>'Select T.c.query('.') as resultfrom @x.nodes('/Root/row') T(c)