SAVING XML documents

SAVING XML documents

Synopsis

The highly respected

professor

Nibbowitz

evidence

that

squid

are more
intelligent

than

cats when

they face

the same challenges

in the same conditions

Retrieve XML from ‘normal’ tables

•
‘normal’ select +
FOR XML

•
Examples


Select * from employees FOR XML RAW

Select * from employees FOR XML
AUTO

Select
* from employees FOR XML
AUTO, elements

Select * from employees FOR XML
PATH

Select * from employees FOR XML
PATH(‘Employee’), root(‘employees’)

Save XML in datbases (MS!)

•
Basic two ways

–
As a text
-
document

•
TYPE: varchar(MAX) or nvarchar(MAX)

•
Benefit: save the document as it is
–

can be retrieved
exact as it was when saved

–
As XML
-
type

•
TYPE: XML

•
Benfit: can search for information in the xml
-
structure
and you can validate the xml
-
document

text

Create table X (


id int primary key not null,


data nvarchar(MAX) not null

)


Insert into X values (2, ‘<bla>hello</bla>’)

Insert into X values
(3, ‘hello’)

Insert into X values
(4,
‘<bla
><inner>hello
</bla
></inner>’)

Insert into X values
(5, ‘<dk>blåbærgrød</dk>’)


NO check
–

no control



XML

Create table X (


id int primary key not null,


data XML not null

)


Insert into X values (2, ‘<bla>hello</bla>’)

Insert into X values
(3, ‘hello’)


Insert into X values
(4,
‘<bla
><inner>hello
</bla
></inner>’)

–

NOT valid

Insert into X values
(5, ‘<dk>blåbærgrød</dk>’)


some check
–

some control



XML
(cont) with schema control

Create table X (


id int primary key not null,


data XML (CONTENT ”schema in DB”) not null

)


Insert into X values (2, ‘<bla>hello</bla>’)

Insert into X values
(3, ‘hello’)
–

NOT valid

Insert into X values
(4,
‘<bla
><inner>hello
</bla
></inner>’)
–

NOT valid

Insert into X values
(5, ‘<dk>blåbærgrød</dk>’)
–

NOT valid


check
–

control validation must follow schema eg <bla>..</bla> is root



XML
(cont) search with XQUERY

Create table X (


id int primary key not null,


data XML
(CONTENT ”schema in DB”) not null

)


Select id, data.query(‘ ”schema reference(CS)”; /CS:bla) as root

From X where ….


Retrieve information from inside the xml document



XML and validation

•
Validation of XML document

–
DTD (document type definition)

•
Benefit easy to understand

–
Schemas (xml schema definition
-

xsd)

•
Benefit: much better type definitions

•
Better control of stucture


•
<?xml version="1.0" encoding="UTF
-
8"?>


•
<!ELEMENT Customers (Customer*)>

•
<!ELEMENT Customer (name, address, phone,
deliverAdr
)>

•
<!ELEMENT name (#PCDATA)>

•
<!ELEMENT address (#PCDATA)>

•
<!ELEMENT phone (#PCDATA)>

•
<!ELEMENT
deliverAdr

(#PCDATA)>


•
<!ATTLIST Customer ID CDATA #IMPLIED>


•
<?xml version="1.0" encoding="UTF
-
8"?>

•
<!DOCTYPE Customers SYSTEM "Customers.dtd">


•
<Customers>

•

<Customer>

•

<name>peter</name>

•

<address>
shjgf
</address>

•

<phone>768797867</phone>

•

<
deliverAdr
>
jkfdhjgl
</
deliverAdr
>

•

</Customer>

•

<Customer ID="3">

•

<name>peter</name>

•

<address>
shjgf
</address>

•

<phone>768797867</phone>

•

<
deliverAdr
>
jkfdhjgl
</
deliverAdr
>

•

</Customer>

•
</Customers>

•
<?xml version="1.0" encoding="UTF
-
8"?>

•
<
xsd:schema

xmlns:xsd
="http://www.w3.org/2001/XMLSchema"

•

targetNamespace
="http://xml.netbeans.org/schema/customers"

•

xmlns:tns
="http://xml.netbeans.org/schema/customers"

•

elementFormDefault
="qualified">

•

<
xsd:element

name="Customers">

•

<
xsd:complexType
>

•

<
xsd:sequence
>

•

<
xsd:element

name="Customer"
minOccurs
="0"
maxOccurs
="unbounded">

•

<
xsd:complexType
>

•

<
xsd:sequence
>

•

<
xsd:element

name="name"/>

•

<
xsd:element

name="address"/>

•

<
xsd:element

name="phone"/>

•

<
xsd:element

name="
deliverAdr
"/>

•

</
xsd:sequence
>

•

<
xsd:attribute

name="ID" type="
xsd:string
"/>

•

</
xsd:complexType
>

•

</
xsd:element
>

•

</
xsd:sequence
>

•

</
xsd:complexType
>

•

</
xsd:element
>

•
</
xsd:schema
>

•
<?xml version="1.0" encoding="UTF
-
8"?>

•
<
c:Customers

xmlns:xsi
='http://
www.w3.org/2001/
XMLSchema
-
instance‘
xmlns:c
='http://
xml.netbeans.org/schema/customers‘
xsi:schemaLocation
='http://xml.netbeans.org/schema/customers
customers.xsd'>

•

<
c:Customer
>

•

<
c:name
>peter</
c:name
>

•

<
c:address
>
shjgf
</
c:address
>

•

<
c:phone
>768797867</
c:phone
>

•

<
c:deliverAdr
>
jkfdhjgl
</
c:deliverAdr
>

•

</
c:Customer
>

•

<
c:Customer

ID="3">

•

<
c:name
>peter</
c:name
>

•

<
c:address
>
shjgf
</
c:address
>

•

<
c:phone
>768797867</
c:phone
>

•

<
c:deliverAdr
>
jkfdhjgl
</
c:deliverAdr
>

•

</
c:Customer
>

•
</
c:Customers
>