XML in SQL

XML:  Extensible MarkUp Language

XSD:

XSLT:

DOM :
Document Object Model

SAX:
simple Apis for xml
1) Faster,  readonly ,fast farward.

SCHMA:
<Book xmlns="urn:Book.xsd">

 Schemma Defination:
           minOccurs="1" maxOccurs="1"
Ex:<xs:element name="Title" type="xs:string" minOccurs="1" maxOccurs="1" />

name is <TagName>
type is DataType
 minOccurs="1" maxOccurs="1" means Tag shoud occur onece only and Mandatory




Slection an XML:

value: Get
Query:
Exist:Get
Node:


select id, bookdata.value('declare namespace x="urn:Book.xsd";
x:Book/x:Title','varchar(20)') from bookcatalogwithsch

create table BookCatalog

(

id int identity primary key,

bookdata xml

)

--works well formed xml

insert bookcatalog values('<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>If Tomorrow Comes</Title>

          <Author>

              <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>')

--shows data but <?xml...?> is stripped

select * from bookcatalog

--insert one more with another processing inst

insert bookcatalog values('<?xml version="1.0" encoding="utf-8"?>

<?xsl-stylesheet href="style.xsl" type="text/xsl"?>

     <Book xmlns="urn:Book.xsd">

          <Title>If Tomorrow Comes</Title>

          <Author>

               <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>'

)

--check data

select * from bookcatalog

--works as it is a valid fragment

insert bookcatalog values('<Book>Some book</Book><Book>One more Book</Book>')

--create a xml schema collection

create xml schema collection myBookSchemaColl

as

'<?xml version="1.0" ?>

<xs:schema targetNamespace="urn:Book.xsd" xmlns="urn:Book.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema"

     attributeFormDefault="qualified" elementFormDefault="qualified">

     <xs:element name="Book">

          <xs:complexType>

              <xs:sequence>

                   <xs:element name="Title" type="xs:string" minOccurs="1" maxOccurs="1" />

                   <xs:element name="Author" type="AuthorType" minOccurs="1" maxOccurs="unbounded" />

                   <xs:element name="Category" type="Category" minOccurs="1" maxOccurs="1" />

                   <xs:element name="Price" type="xs:decimal" minOccurs="1" maxOccurs="1" />

                   <xs:element name="Synopsis" type="xs:string" minOccurs="0" maxOccurs="1" />

              </xs:sequence>

          </xs:complexType>

     </xs:element>

     <xs:simpleType name="Category">

          <xs:restriction base="xs:string">

              <xs:enumeration value="Horror" />

              <xs:enumeration value="Classic" />

              <xs:enumeration value="Fiction" />

              <xs:enumeration value="Business" />

              <xs:enumeration value="Computers" />

          </xs:restriction>

     </xs:simpleType>

     <xs:complexType name="AuthorType">

          <xs:sequence>

              <xs:element name="FirstName" type="xs:string" minOccurs="1" maxOccurs="1" />

              <xs:element name="LastName" type="xs:string" minOccurs="1" maxOccurs="1" />

          </xs:sequence>

     </xs:complexType>

</xs:schema>

'

--create table with schema for validation

create table BookCatalogWithSch

(

id int primary key,

bookdata xml (myBookSchemaColl)

)

--insert invalid data

insert bookcatalogwithsch values(1,'<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Author>

              <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Title>If Tomorrow Comes</Title>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>')

--insert valid data

insert bookcatalogwithsch values(1,'<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>If Tomorrow Comes</Title>

          <Author>

              <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>')

--but can yet insert doc frag

--insert invalid data

insert bookcatalogwithsch values(2,'<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>If Tomorrow Comes</Title>

          <Author>

              <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>

     <Book xmlns="urn:Book.xsd">

          <Title>If Tomorrow Comes</Title>

          <Author>

              <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>')

select * from bookcatalogwithsch

--create the bookcatalog table with a schema to store only documents

drop table bookcatalogwithsch

--create table with schema for validation

create table BookCatalogWithSch

(

id int primary key,

bookdata xml (document myBookSchemaColl)

)

--attempt insert again

--but can yet insert doc frag

--insert invalid data

insert bookcatalogwithsch values(2,'<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>If Tomorrow Comes</Title>

          <Author>

              <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>

     <Book xmlns="urn:Book.xsd">

          <Title>If Tomorrow Comes</Title>

          <Author>

              <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>')

-- try this with untyped xml col

create table testxml

(

id int,

x xml(document)

)

--drop table testxml

-- use constraint to achieve this

create table testxml

(

id int,

x xml constraint xml_root check(x.value('count(/*)','int') = 1)

)

-- now try inserting

insert testxml values(2,'<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>If Tomorrow Comes</Title>

          <Author>

              <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>

     <Book xmlns="urn:Book.xsd">

          <Title>If Tomorrow Comes</Title>

          <Author>

              <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>')

--insert remaining rows

insert bookcatalogwithsch values(3,'<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>.Net Framwork Programming</Title>

          <Author>

              <FirstName>Jeffery</FirstName>

              <LastName>Richter</LastName>

          </Author>

          <Category>Computers</Category>

          <Price>1050</Price>

          <Synopsis>Great book giving internals of .net framework</Synopsis>

     </Book>')

insert bookcatalog values('<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>.Net Framwork Programming</Title>

          <Author>

              <FirstName>Jeffery</FirstName>

              <LastName>Richter</LastName>

          </Author>

          <Category>Computers</Category>

          <Price>1050</Price>

          <Synopsis>Great book giving internals of .net framework</Synopsis>

     </Book>')

insert bookcatalogwithsch values(3,'<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>Introduction to SQL Server 2005</Title>

          <Author>

              <FirstName>Bob</FirstName>

              <LastName>Beauchamein</LastName>

          </Author>

          <Author>

              <FirstName>Dan</FirstName>

              <LastName>Miller</LastName>

          </Author>

          <Category>Computers</Category>

          <Price>2500</Price>

          <Synopsis>Good insight into SQL Server 2005 new features and utilization</Synopsis>

     </Book>')

insert bookcatalog values('<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>Introduction to SQL Server 2005</Title>

          <Author>

              <FirstName>Bob</FirstName>

              <LastName>Beauchamein</LastName>

          </Author>

          <Author>

              <FirstName>Dan</FirstName>

              <LastName>Miller</LastName>

          </Author>

          <Category>Computers</Category>

          <Price>2500</Price>

          <Synopsis>Good insight into SQL Server 2005 new features and utilization</Synopsis>

     </Book>')

insert bookcatalogwithsch values(4,'<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>Our Lifetime Together</Title>

          <Author>

              <FirstName>Tom</FirstName>

              <LastName>Cat</LastName>

          </Author>

          <Author>

              <FirstName>Jerry</FirstName>

              <LastName>Mouse</LastName>

          </Author>

          <Category>Classic</Category>

          <Price>500</Price>

          <Synopsis>The cat and mouse game</Synopsis>

     </Book>')

insert bookcatalog values('<?xml version="1.0" encoding="utf-8"?>

     <Book xmlns="urn:Book.xsd">

          <Title>Our Lifetime Together</Title>

          <Author>

              <FirstName>Tom</FirstName>

              <LastName>Cat</LastName>

          </Author>

          <Author>

              <FirstName>Jerry</FirstName>

              <LastName>Mouse</LastName>

          </Author>

          <Category>Classic</Category>

          <Price>500</Price>

          <Synopsis>The cat and mouse game</Synopsis>

     </Book>')

insert bookcatalogwithsch values(1,'<Book xmlns="urn:Book.xsd">

          <Title>If Tomorrow Comes</Title>

          <Author>

              <FirstName>Sidney</FirstName>

              <LastName>Sheldon</LastName>

          </Author>

          <Category>Fiction</Category>

          <Price>200</Price>

          <Synopsis>Good story</Synopsis>

     </Book>')

insert bookcatalogwithsch values(2,'<Book xmlns="urn:Book.xsd">

          <Title>.Net Framwork Programming</Title>

          <Author>

              <FirstName>Jeffery</FirstName>

              <LastName>Richter</LastName>

          </Author>

          <Category>Computers</Category>

          <Price>1050</Price>

          <Synopsis>Great book giving internals of .net framework</Synopsis>

     </Book>')

insert bookcatalogwithsch values(5,'

<Book xmlns="urn:Book.xsd">

          <Title>Nothingness</Title>

          <Author>

              <FirstName>Nobody</FirstName>

              <LastName>AtAll</LastName>

          </Author>

          <Category>Horror</Category>

          <Price>1050</Price>

     </Book>'

)

insert bookcatalog values('

<Book xmlns="urn:Book.xsd">

          <Title>Nothingness</Title>

          <Author>

              <FirstName>Nobody</FirstName>

              <LastName>AtAll</LastName>

          </Author>

          <Category>Horror</Category>

          <Price>1050</Price>

     </Book>'

)

select * from bookcatalogwithsch

select * from bookcatalog

--using the value() fn

select id, bookdata.value('declare namespace x="urn:Book.xsd";

x:Book/x:Title','varchar(20)') from bookcatalogwithsch

select id, bookdata.value('declare namespace x="urn:Book.xsd" ; /x:Book[1]/x:Title[1]','varchar(20)') from bookcatalog

--using the query() fn

select id, bookdata.query('declare namespace x= "urn:Book.xsd"; /x:Book/x:Title') from bookcatalogwithsch

where bookdata.value('declare namespace x= "urn:Book.xsd"; count(/x:Book/x:Author)','int')>1

--using the exist() fn

select id, bookdata.query('declare  namespace x= "urn:Book.xsd" ; /x:Book/x:Title') from bookcatalogwithsch

where bookdata.exist('declare namespace x= "urn:Book.xsd" ; /x:Book/x:Synopsis')=1

select id, bookdata.query('declare  namespace x = "urn:Book.xsd" ;

/x:Book/x:Author/x:FirstName') from bookcatalogwithsch

--using the nodes() fn

select t.acol.query('declare  namespace x= "urn:Book.xsd"; x:FirstName')

from bookcatalogwithsch

cross apply

bookdata.nodes('declare namespace x= "urn:Book.xsd" ;/x:Book/x:Author') as t(acol)

--FLOWR

select bookdata.query('declare  namespace x= "urn:Book.xsd";

for $b in /x:Book

where $b[x:Price>500]

  order by $b/x:Price

return $b/x:Author')

from Bookcatalogwithsch

--using xpath

select bookdata.query('declare namespace x= "urn:Book.xsd"; /x:Book[x:Price>500]/x:Author')

from Bookcatalogwithsch

--in the correct format using constructors

select bookdata.query('declare namespace b= "urn:Book.xsd";

for $b in /b:Book

return <Authors>{$b/b:Author}</Authors>')

from Bookcatalogwithsch

--strip the namespace using constructors

select bookdata.query('declare namespace b= "urn:Book.xsd" ;

for $b in /b:Book

return <Authors>{data($b/b:Author/b:FirstName)}</Authors>')

from Bookcatalogwithsch

--modify data in place

--insert an author

update bookcatalogwithsch

set bookdata.modify('declare namespace x = "urn:Book.xsd" ;

insert <x:Author><x:FirstName>Mickey</x:FirstName><x:LastName>Mouse</x:LastName></x:Author>

after (/x:Book/x:Author[1])')

where id=5

select * from bookcatalogwithsch where id = 5

--delete teh same

update bookcatalogwithsch

set bookdata.modify('declare   namespace x= "urn:Book.xsd" ;

delete (/x:Book/x:Author[x:FirstName="Mickey"])')

where id=5

select * from bookcatalogwithsch

--edit in place

update bookcatalogwithsch

set bookdata.modify('declare namespace x = "urn:Book.xsd" ;

replace value of (/x:Book/x:Price)

with 100')

where id=5

select * from bookcatalogwithsch where id=5