SAVING XML documents

nutmegactSoftware and s/w Development

Nov 11, 2012 (4 years and 9 months ago)

281 views

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
>