SQL Server 2008 XML Example

SQL Server 2008 XML Example
Suppose you would like to store XML in the following example structure in your database:

<TABLE>
<TR Name="Row1">
<TD Name="Col1">4</TD>
<TD Name="Col2">5</TD>
</TR>
<TR Name="Row2">
<TD Name="Col1">6</TD>
<TD Name="Col2">7</TD>
</TR>
</TABLE>

Here is some example SQL to create a schema, create a table, insert xml data, and query the data:

CREATE XML SCHEMA COLLECTION TableSchemaCollection AS
N'<?xml version="1.0" encoding="utf-16"?>
<xsd:schema
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >

<xsd:element name="TABLE">
<xsd:complexType mixed="true">
<xsd:sequence>
<xsd:element name="TR" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType mixed="true" >
<xsd:sequence>
<xsd:element name="TD" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType mixed="true">
<xsd:simpleContent>
<xsd:extension base="xsd:string">
<xsd:attribute name="Name" type="xsd:string" use="required"/>
</xsd:extension>
</xsd:simpleContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="Name" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>' ;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[XmlEntries](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Answer] xml (TableSchemaCollection) NULL,
CONSTRAINT [PK_EntryXml] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
-- insert some data
INSERT INTO XmlEntries
(Answer)
VALUEs ('<TABLE><TR Name="Row1"><TD Name="Col1">1</TD></TR></TABLE>')
INSERT INTO XmlEntries
(Answer)
VALUEs ('<TABLE><TR Name="Row2"><TD Name="Col1">1</TD><TD Name="Col2">2</TD></TR><TR Name="Row1"><TD Name="Col1">1</TD><TD Name="Col2">2</TD></TR></TABLE>')
INSERT INTO XmlEntries
(Answer)
VALUEs ('<TABLE><TR Name="Row1"><TD Name="Col1">4</TD><TD Name="Col2">5</TD></TR><TR Name="Row2"><TD Name="Col1">6</TD><TD Name="Col2">7</TD></TR></TABLE>')

-- some query examples

Select LEN(CAST (Answer as varchar(max))
), CAST (Answer as varchar(max)) AS xmltext, * from XmlEntries

-- ANSI_PADDING must be on to use query keyword
SET ANSI_PADDING ON;
SELECT
Answer.query('/TABLE/TR/TD')
FROM
XmlEntries

SELECT
Answer.query('/TABLE/TR[@Name="Row1"]')
FROM
XmlEntries

SELECT
Answer.query('/TABLE/TR/TD[@Name="Col1"]')
FROM
XmlEntries

SELECT
Answer.value('(/TABLE/TR[@Name="Row1"]/TD[@Name="Col1"])[1]', 'int')
FROM
XmlEntries

SELECT
SUM(Answer.value('(/TABLE/TR[@Name="Row1"]/TD[@Name="Col1"])[1]', 'int'))
FROM
XmlEntries

SELECT
Answer.query('/TABLE')
FROM
XmlEntries

Created By: amos 4/12/2013 2:40:53 PM
Updated: 4/12/2013 2:57:32 PM