[DRAFT]

XML Basics for SQL Server

Contents:

XML Basics for SQL Server

Two types of XML mapping

Attribute-centric mapping (0, 1)

Element-centric mapping (2)

Two main SQL keywords related to XML:

Two types of XML mapping

1. Attribute-centric mapping (0, 1)

2. Element-centric mapping (2)

Attribute-centric mapping (0, 1)

'<catalog>

<book id="bk101"

<author="Gambardella, Matthew"

<title="XML Developers Guide"

<genre="Computer"

<price="44.95"

<publish_date="2000-10-01"

<description = "An in-depth look at creating applications with XML.">

</book>

</catalog>'

Code examples:

DECLARE @iDoc int

EXECUTE sp_xml_preparedocument @iDoc OUTPUT, '<catalog>

<book id="bk101"

author="Gambardella, Matthew"

title="XML Developers Guide"

genre="Computer"

price="44.95"

publish_date="2000-10-01"

description = "An in-depth look at creating applications with XML.">

</book>

</catalog>'

SELECT * FROM OpenXML(@iDoc, '/catalog/book', 0)

WITH

(author varchar(50),

title varchar(50),

genre varchar(50),

price varchar(50),

publish_date varchar(50),

description varchar(1000)

)

Element-centric mapping (2)

'<catalog>

<book id="bk101">

<author>Gambardella, Matthew</author>

<title>XML Developers Guide</title>

<genre>Computer</genre>

<price>44.95</price>

<publish_date>2000-10-01</publish_date>

<description>An in-depth look at creating applications

with XML.</description>

</book>

</catalog>'

Code examples:

DECLARE @iDoc int

EXECUTE sp_xml_preparedocument @iDoc OUTPUT, '<catalog>

<book id="bk101">

<author>Gambardella, Matthew</author>

<title>XML Developers Guide</title>

<genre>Computer</genre>

<price>44.95</price>

<publish_date>2000-10-01</publish_date>

<description>An in-depth look at creating applications with XML.</description>

</book>

</catalog>'

SELECT * FROM OpenXML(@iDoc, '/catalog/book', 2)

WITH

(author varchar(50),

title varchar(50),

genre varchar(50),

price varchar(50),

publish_date varchar(50),

description varchar(1000)

)

Main SQL keywords related to XML:

1. OpenXML

2. FOR XML

3. OpenROWSET

• OpenXML: is to read the XML data
• FOR XML: is to generate a XML data
• OpenROWSET: is to ......