MS Sql 70-461: Chapter 7
Les 1
XML is hoofdlettergevoelige tekst in unicode. Speciale tekens in XML kunnen als volgt worden escaped:
& &
" "
< <
> >
' '
Ook kan er gebruik gemaakt worden van
Als een gedeelte van XML geen root node heeft, dan wordt gesproken over een XML fragment.
Als elementen attributen hebben, dan wordt gesproken over attribute-centric presentatie. Als er geen attributen in de XML zitten, dan heet dit element-centric presentation.
Met XML Scheme Descrition (XSD) kan de metadata van het XML document worden beschreven. In een XSD worden de structuur en de data types vastgelegd per element (tag). Als een XSD document bestaat voor een XML document, dan wordt gesproken over een typed XML document.
FOR XML RAW
De eerse manier om van het resultaat van een query XML te maken is door de RAW optie. In RAW mode wordt elke rij in de resultaatset geconverteerd naar een element met de naam row en de kolommen van de rij worden opgenomen als attributen van dat element.
FOR XML AUTO
De FOR XML AUTO retourneert XML met geneste elementen. In de AUTO en RAW mode kan het keyword ELEMENTS gebruikt worden om element-centric XML te produceren. Met de WITH NAMESPACES clause (welke geplaatst voor/boven de SELECT clause) definieert de namespace en aliases van de geretourneerde XML:
WITH XMLNAMESPACES('TK461-CustomersOrders' AS co)
SELECT
[co:Customer].custid AS [co:custid],
[co:Customer].companyname AS [co:companyname],
[co:Order].orderid AS [co:orderid],
[co:Order].orderdate AS [co:orderdate]
FROM Sales.Customers AS [co:Customer]
INNER JOIN Sales.Orders AS [co:Order]
ON [co:Customer].custid = [co:Order].custid
ORDER BY [co:Customer].custid, [co:Order].orderid
FOR XML AUTO, ELEMENTS, ROOT('CustomersOrders');
Output:
Let bij het genereren van XML op de ORDER BY clause. Zonder het gebruik van de ORDER BY zijn de volgorde van de rijen onvoorspelbaar en kan dit vreemde XML genereren. De FOR XML cluase komt na de ORDER BY clause.
Ook de volgorde van de kolommen in de SELECT clause heeft invloed op de gegenereerde XML. Sql Server gebruikt de volgorde van de kolommen om de geneste elementen te bepalen. De volgorde van de kolommen zouden 1-op-veel relaties moeten zijn. Een customer kan meerdere orders hebben en daarom komen de customer kolommmen eerst, gevolgd door de order kolommen.
In de RAW en AUTO mode kan ook het XSD schema worden geretourneerd. Dit schema wordt dan in de XML opgenomen en heet daarom een inline schema. De XSD wordt in de XML opgenomen met de XMLSCHEMA directive. Als enkel het XSD schema benodigd is, zorg dan voor een query die geen resultaten teruggeeft:
SELECT [Customer].custid AS [custid],
[Customer].companyname AS [companyname],
[Order].orderid AS [orderid],
[Order].orderdate AS [orderdate]
FROM Sales.Customers AS [Customer]
INNER JOIN Sales.Orders AS [Order]
ON [Customer].custid = [Order].custid
WHERE 1 = 2
FOR XML AUTO, ELEMENTS,
XMLSCHEMA('TK461-CustomersOrders');
Output:
xmlns:xsd=http://www.w3.org/2001/XMLSchema
xmlns:sqltypes=http://schemas.microsoft.com/sqlserver/2004/sqltypes
elementFormDefault="qualified">
schemaLocation=http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd
/>
sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth"
sqltypes:sqlSortId="52">
FOR XML PATH
Met FOR XML PATH en FOR XML EXPLICIT kan handmatig de gerourneerde XML bepaald worden. de EXPLICIT mode bestaat enkel nog voor backwards compatibiliteit.
In de PATH mode dienen kolomnamen en aliases als XPath expressies. XPath expressies definieren het pad naar het element in de gegenereerde XML. Path wordt uitgedrukt op een hierarchische manier. De levels worden gescheiden door een slash (/). Standaard wordt elke kolom een element, maar als atrtributen nodig zijn, kan voor de alias een @ gezet worden:
SELECT Customer.custid AS [@custid], Customer.companyname AS [companyname]
FROM Sales.Customers AS Customer
WHERE Customer.custid <= 2
ORDER BY Customer.custid
FOR XML PATH ('Customer'), ROOT('Customers');
Output:
De parameter van PATH geeft aan wat de naam van de tag moet zijn van de kolommen die in de SELECT zijn opgenomen (Customer). Met ROOT wordt de naam van het root element opgegeven (Customers).
Als XML gemaakt moet worden met geneste elementen voor child tabellen, dan kan dit met subqueries in de SELECT clause. De subqueries moeten dan één waarde per kolom teruggeven in de SELECT clause.
Met de TYPE directive - als in FOR XML PATH, TYPE - wordt het resultaat van de query teruggegeven alszijnde het type XML en niet als tekst
Shredding XML to tables
Het converteren van XML naar relationele tabellen heet shredding XML.
De OPENXML functie biedt een rowset view aan op basis van een XML document door gebruik te maken van het Document Object Model (DOM). Voordat de DOM geparsed kan worden, moet deze voorbereid worden. Om de DOM-presentatie voor te bereiden voor XML, moet de stored procedure (SP) sys.sp_xml_preparedocument aangeroepen worden. Nadat het document geshred is, moet de DOM presentatie verwijderd worden m.b.v. de sp sys.sp+xml_removedocument.
De OPENXML functie kent de volgende parameters:
- Een XML DOM handle, welke door de sp_xml_preparedocumentwordt teruggegeven
- Een XPath expressie om de nodes te vinden die gemapt moeten worden naar rijen binnen de rowset
- Een beschrijving van de gerourneerde rowset
- Een mapping tussen de XML nodes en de rowset kolommen.
Met de WITH clause kunnen XML elementen of attributen gemapt worden naar rijen en kolommen. In deze clause kan een bestaande tabel gespecificeerd worden, welke dan als een template gebruikt wordt of er kan een nieuwe tabel definitie opgeven, waarvan de syntax gelijk is aan de CREATE TABLE statement.
De OPENXML functie accepteert ook een derde optionele parameter, genaamd flags. Flags met waarde 1 betekent attribute-centric mapping en 0 betekent element=centric mapping en 3 betekent beide. Waarde 8 kan gecombineerd worden met waarde 1 en 2 met een bitwise OR operator:
-- Create an internal representation
EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
-- Attribute-centric mapping
SELECT *
FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',1)
WITH (custid INT,
companyname NVARCHAR(40));
-- Element-centric mapping
SELECT *
FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',2)
WITH (custid INT,
companyname NVARCHAR(40));
-- Attribute- and element-centric mapping
-- Combining flag 8 with flags 1 and 2
SELECT *
FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',11)
WITH (custid INT,
companyname NVARCHAR(40));
-- Remove the DOM
EXEC sys.sp_xml_removedocument @DocHandle;
GO
Output:
custid companyname
----------- ----------------------------------------
1 NULL
2 NULL
custid companyname
----------- ----------------------------------------
NULL Customer NRZBB
NULL Customer MLTDN
custid companyname
----------- ----------------------------------------
1 Customer NRZBB
2 Customer MLTDN
De nodes methode van het XML data type is efficienter als een XML document eenmalig geshred moet worden naar een tabel. Als hetzelfde XML-document meerdere keren moet worden geshred, dan is OPENXML geschikter.
Les 2
Xquery is een standaard taal voor het browsen door XML en retourneert XML. Het is rijker uitgeruct dan de oudere standaard XPath. Met XPath kan enkel genavigeerd worden door XML. Dit kan ook met XQuery, maar met XQuery kan ook geitereerd worden over nodes, kan de vorm bepaald worden van de XML die wordt geretourneerd en meer.
Voor een query taal is een query-processing engine nodig. De Sql Server database engine verwerkt XQuery binnenin T-Sql statements door d.m.v. XML data type methoden. Niet alle XQuery features worden ondersteund door Sql Server, bijv: user defined functies omdat dit al kan met CLR functies.
XQuery basics
XQuery is hoofdletter gevoelig. XQuery retourneert sequences en een sequence bevat atomic waarden of complex waarden (XML nodes). Elke node zoals een element, attribute, tekst, processing instructie, commentaar of document kan onderdeel zijn van een sequence.
2
SELECT
@x.query('*') AS Complete_Sequence,
@x.query('data(*)') AS Complete_Data,
@x.query('data(root/a/c)') AS Element_c_Data;
Output
Complete_Sequence Complete_Data Element_c_Data
--------------------------------------------- ------------- --------------
Elke identifier in XQuery is een qualified name, of, QName. Een QName bestaat uit de local name en optioneel de prefix van een namespace. In voorgaand voorbeeld zijn root, a, b, c en d QNames. De volgende namespaces zijn predefined in Sql Server:
- xs: de namespace voor een XML schema
- xsi: de XML schema instance namespace. Wordt gebruikt om XML schema's te verbinden met instance documenten
- xdt: de namespace voorXPath en XQuery data types
- fn: de namespace voor functies
- sqltypes: de namespace die mapping voor Sql Server data types biedt
- xml: de standaard namespace
Eigen data types kunnen gedefinieerd worden in de prolog, welke thuishoren aan het begin van de XQuery. Ook kan er commentaar gebruikt worden in XQuery expressies. Voorbeelden:
DECLARE @x AS XML;
SET @x='
-- Namespace in prolog of XQuery
SELECT
@x.query('
(: explicit namespace :)
declare namespace co="TK461-CustomersOrders";
//co:Customer[1]/*
') AS [Explicit namespace];
-- Default namespace for all elements in prolog of XQuery
SELECT
@x.query('
(: default namespace :)
declare default element namespace "TK461-CustomersOrders";
//Customer[1]/*
') AS [Default element namespace];
-- Namespace defined in WITH clause of T-SQL SELECT
WITH XMLNAMESPACES('TK461-CustomersOrders' AS co)
SELECT
@x.query('
(: namespace declared in T-SQL :)
//co:Customer[1]/*
') AS [Namespace in WITH clause];
Verkorte output
Explicit namespace
--------------------------------------------------------------------------------
Default element namespace
--------------------------------------------------------------------------------
XQuery Data Types
XQuerygebruikt ongeveer 50 voorgedefinieerde data types. XQuery data types zijn onderverdeeld in node types en atomic types. Node types zijn eerder in de hoofdstuk al opgesomd en de meest belangrijke atomic types zijn: xs:boolean, xs:string, xs:QName, xs:date, xs:time, xs:datetime, xs:float, xs;double, xs:decimal en xs:integer. XQuery kent zijn eigen type system.
XQuery functies
XQuery kent vele functies. De functies zijn onder te verdelen in de volgende categorieen:
- numerieke functies: ceiling(), floor() en round()
- string functies: concat(), contains(), substrung(), string-length(), lower-case() en upper-case()
- boolean en boolean constructor functies: not(), true() en false()
- Node functies: local-name() an namespace-uri()
- data accessor functies: data() en string()
- Sql server extension functies: sql:column() en sql:variable()
DECLARE @x AS XML;
SET @x='
SELECT
@x.query('
for $i in //Customer
return
{ $i/@companyname }
{ count($i/Order) }
{ max($i/Order/@orderid) }
');
De iteratie die gebruikt wordt in voorgaand voorbeeld is een XQuery FLWOR expressie en bepaald de structuur van de XML die wordt teruggegeven.
Navigation
Met XQuery kan een pad gespecificeerd worden absoluut of relatief gezien vanaf de huidige node. Elk pad bestaat uit een volgorde van stappen, die opgesomd staan van links naar rechts:
node-name/child::element-name[@attribuut-naam=waarde]
Stappen zijn gescheiden d.m.v. de slash (/). Bovenstaand voorbeeld kent dus twee stappen. Een stap kan bestaan uit drie delen:
1) Axis: Specificeert de richting. In het voorbeeld is de axis child::
2) Node test: Specificeert de criteria voor het selecteren van nodes. In het voorbeeld is element-name de node test. Enkel nodes die element-name heten worden geselecteerd
3) Predicate: Enkel nodes die voldoen aan de predicate worden geselecteerd. VB: [@orderId=298]
Axes die in Sql Server worden indersteund zijn:
child:: - Geef alle childs terug van de huidige node. Dit is de standaard axis en kan daarom weggelaten worden
descendant:: Geef alle afstammelingen terug
self:: Geed de context van de huidige node
descendant-or-self:: (afgekort //) Geef de context node en alle afstammelingen
attribute:: (afgekort @) Geef de opgegeven attribuut
parent:: (afgekort ..) Geef de parent van de context node
Een node test (punt 2 van bovenstaande opsomming) volgt de opgegeven axis. Wildcards zijn ook toegestaan. Een asterisk betekent dat alle principal nodes worden geselecteerd. Er kunnen ook testen worden uitgevoerd op soort:
- commant(): selecteer commentaar nodes
- node(): elke node. Niet te verwarren met de wildcard *, welke principal node betekent en node() pakt alle nodes
- processing-instruction(): selecteert alle processing instruction nodes
- text(): Geeft alle text nodes of nodes zonder tags
Predicates
Standaard predicates zijn numerieke en boolean predicates. Numerieke predicates selecteren nodes o.b.v. hun positie. Deze selecteer je m.b.v. brackets. VB: /x/y[1]. Dit betekent: selecteer de eerste child van het y element van elk x element.
Boolean predicates selecteren alle nodes als de predicate true evalueert. Als een atomic value van een sequenco voldoet aan een predicate, dan wordt de gehele sequence teruggegeven.
DECLARE @x AS XML = N'';
SELECT @x.query('(1, 2, 3) = (2, 4)'); -- true omdat 2 voorkomt in beide sequences
SELECT @x.query('(5, 6) < (2, 4)'); -- false omdat geen enkele waarde in de linker sequence is kleiner dan 2 of 4
SELECT @x.query('(1, 2, 3) = 1'); -- true omdat er een atomische waarde in de linker sequence is die 1 bevat
SELECT @x.query('(1, 2, 3) != 1'); -- true omdat in de linker sequence niet alle waarden ongelijk zijn aan 1
Value operators zijn tekstuele operators en kunnen niet worden toegepast op sequences. General comparison operators zijn operators die gebruikt zijn in bovenstaand voorbeeld. De value operators zijn:
General Value Description
= eq equal
!= ne not equal
< lt less than
<= le less than or equal to
> gt greater than
>= ge greater than or equal to
Voorbeelden:
DECLARE @x AS XML = N'';
SELECT @x.query('(5) lt (2)'); -- false
SELECT @x.query('(1) eq 1'); -- true
SELECT @x.query('(1) ne 1'); -- false
GO
DECLARE @x AS XML = N'';
SELECT @x.query('(2, 2) eq (2, 2)'); -- error omdat een value operator wordt toegepast op een sequence
GO
XQuery ondersteund ook de if.. then.. else.. expressie. Deze is vergelijkbar met de CASE expression van T-Sql:
DECLARE @x AS XML = N'
DECLARE @v AS NVARCHAR(20) = N'FirstName';
SELECT
@x.query('
if (sql:variable("@v")="FirstName") then
/Employee/FirstName
else
/Employee/LastName
') AS FirstOrLastName;
GO
Afhankelijk van de variabele @v wordt of de FIrstName geselecteerd of de LastName.
FLWOR Expressions
De echte krach van XQuery ligt in de FLWOR expression, wat een acroniem is voor For Let Where Order by Return. Een FLWOR expressie is eigenlijk een for each loop. De FLWOR expressie bestaat uit de volgende onderdelen:
FOR
Met een FOR clause worden iterator variabelen gekoppeld aan input sequences.
LET
Met de optionele LET clause kunnen waarden worden toegewezen aan een variabele voor een specifieke iteratie.
WHERE
Optioneel en hiermee kan de iteratie gefilterd worden
ORDER BY
Met de ORDER BY clause kan de volgorde worden bepaald waarin geitereerd wordt.
RETURN
De RETURN clause wordt per iteratie geevalueerd en de resultaten worden geretourneerd in dezelfde volgorde als dat er geitereerd is. Met deze clause wordt de structuur van de XML bepaald.
SELECT
@x.query('for $i in CustomersOrders/Customer/Order
let $j := $i/orderdate
where $i/@orderid < 10900
order by ($j)[1]
return
{$j}
') AS [Filtered, sorted and reformatted orders with let clause];
De naam van een iterator variabele moet altijd met een @ beginnen. Alles tussen accolades wordt geparsed. Als deze accolades er niet zouden staan, wordt het als gewone tekst beschouwd.
Les 3
Met het XML datatype komen velen voordelen ten opzicht van XML opslaan als tekst. Een voorbeeld hiervan is het genereren van een execution plan in XML en te gebruiken via statements SET SHOWPLAN_XML en SET STATISTICS XML. Met USE PLAN kan de optimizer gedwongen een opgegeven execution plan te gebruiken.
XML Data Type methods
Een XML data type methode bevat vijf methoden die een XQuery als parameter accepteren: query(), value(), exist(), modify() en nodes().
De value() methode retourneert een scalar waarde. De eerste parameter van deze functie is een XQuery expressie en de tweede Sql Server data type.
De exist() methode kan gebruikt worden om te bepalen of een node bestaat in een XML instance.
De query() methode wordt gebruikt om de XML data te query'en.
De modify() methode wordt gebruikt om de XML bij te werken en wordt gebruikt in de UPDATE clause
De nodes() methode is bruikbaar als XML geshred moet worden naar een tabel. Het doel is hetzelfde als beschreven in Les 1, maar de nodes() methode is in het algemeen sneller. Deze methode prepareert de DOM intern tijdens de uitvoering van de SELECT clause. De nodes() methode moet worden aangeroepen voor elke rij in de tabel.
Reacties
Een reactie posten