Similar Topics...
 |
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
|
|
|
|
|
|