SQL Server/T-SQL Tutorial/XML/XML
Содержание
Create index on XML column
3> CREATE TABLE dbo.VisioDocs
4> (
5> id INT NOT NULL,
6> doc XML NOT NULL
7> );
8> GO
1>
2>
3> ALTER TABLE dbo.VisioDocs
4> ADD CONSTRAINT PK_VisioDocs PRIMARY KEY CLUSTERED (id);
5> GO
1> CREATE PRIMARY XML INDEX idx_xml_primary ON dbo.VisioDocs(doc);
2> GO
Msg 1934, Level 16, State 1, Server J\SQLEXPRESS, Line 1
CREATE XML INDEX failed because the following SET options have incorrect settings: "QUOTED_IDENTIFIER". Verify that SET options are correct for use with indexed views and/or indexes on computed column
s and/or query notifications and/or xml data type methods.
1> CREATE XML INDEX idx_xml_path ON VisioDocs(doc)
2> USING XML INDEX idx_xml_primary
3> FOR PATH;
4> GO
Msg 1934, Level 16, State 1, Server J\SQLEXPRESS, Line 1
CREATE XML INDEX failed because the following SET options have incorrect settings: "QUOTED_IDENTIFIER". Verify that SET options are correct for use with indexed views and/or indexes on computed column
s and/or query notifications and/or xml data type methods.
1>
2> drop table VisioDocs;
3> GO
Creating xml Data Type Columns and insert data
5>
6> CREATE TABLE dbo.Book(
7> BookID int IDENTITY(1,1) PRIMARY KEY,
8> ISBN char(10) NOT NULL,
9> BookName varchar(250) NOT NULL,
10> AuthorID int NOT NULL,
11> ChapterDESC XML NULL)
12> GO
1>
2> CREATE PROCEDURE dbo.usp_INS_Book
3> @ISBN char(10),
4> @BookName varchar(250),
5> @AuthorID int,
6> @ChapterDESC xml
7> AS
8> INSERT dbo.Book
9> (ISBN, BookName, AuthorID, ChapterDESC)
10> VALUES (@ISBN, @BookName, @AuthorID, @ChapterDESC)
11> GO
1>
2> --Inserting xml Data into a Column
3>
4> INSERT dbo.Book
5> (ISBN, BookName, AuthorID, ChapterDESC)
6> VALUES ("1111","SQL Server",55,
7> CAST("<Book name="SQL Server">
8~ <Chapters>
9~ <Chapter id="1"> a </Chapter>
10~ <Chapter id="2"> b </Chapter>
11~ <Chapter id="3"> c </Chapter>
12~ <Chapter id="4"> d </Chapter>
13~ <Chapter id="5"> e </Chapter>
14~ <Chapter id="6"> f </Chapter>
15~ </Chapters>
16~ </Book>" as XML))
17> GO
(1 rows affected)
1>
2>
3> select * from dbo.book
4> GO
BookID ISBN BookName
AuthorID ChapterDESC
----------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------- ----------- ------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------
1 1111 SQL Server
55 <Book name="SQL Server"><Chapters><Chapter id="1"> a </Chapter><Chapter id="2"> b </Chapter><Chapter id="3"> c </C
hapter><Chapter id="4"> d </Chapter><Chapter id="5"> e </Chapter><Chapter id="6"> f </Chapter></Chapters></Book>
(1 rows affected)
1> drop table dbo.book
2> GO
1>
Insert XML data to a XML type column
2> CREATE TABLE dbo.Contacts
3> (
4> contactid INT NOT NULL PRIMARY KEY,
5> contactname NVARCHAR(50) NOT NULL,
6> I18N BIT NOT NULL,
7> otherattributes XML NOT NULL
8> );
9> GO
1>
2>
2> INSERT INTO dbo.Contacts VALUES(1, "Mike", 1, N"<I18N xmlns="I18N"><ID>1</ID><FL>S</FL></I18N>");
3> INSERT INTO dbo.Contacts VALUES(2, "Her", 0, N"<Foreigns xmlns="Foreign"><NL>German</NL><ENG>1</ENG></Foreigns>");
4> INSERT INTO dbo.Contacts VALUES(3, N"Richard", 1, N"<I18N xmlns="I18N"><ID>2</ID><FL>German</FL></I18N>");
5> INSERT INTO dbo.Contacts VALUES(4, N"Gianluca", 0, N"<Foreigns xmlns="Foreign"><NL>Italian</NL><ENG>1</ENG></Foreigns>");
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> drop table dbo.Contacts;
3> GO
Insert XML data to database
Retrieving XML Data
5>
6> CREATE TABLE dbo.ProductBilling
7> (ProductBillingID int IDENTITY(1,1) PRIMARY KEY,
8> ProductBillingXML XML NOT NULL)
9> GO
1>
2> INSERT dbo.ProductBilling(ProductBillingXML)
3> VALUES ("<ProductBilling Billingnumber="1" customerid="22" orderdate="7/1/2005">
4~ <OrderItems>
5~ <Item id="22" qty="1" name="SQL"/>
6~ <Item id="24" qty="1" name="T-SQL"/>
7~ </OrderItems>
8~ </ProductBilling>")
9>
10> INSERT dbo.ProductBilling
11> (ProductBillingXML)
12> VALUES ("<ProductBilling Billingnumber="1" customerid="40" orderdate="7/11/2005">
13~ <OrderItems>
14~ <Item id="11" qty="1" name="Notes"/>
15~ </OrderItems>
16~ </ProductBilling>")
17>
18> INSERT dbo.ProductBilling
19> (ProductBillingXML)
20> VALUES ("<ProductBilling Billingnumber="1" customerid="9" orderdate="7/22/2005">
21~ <OrderItems>
22~ <Item id="11" qty="1" name="SQL Notes"/>
23~ <Item id="24" qty="1" name="T-SQL Notes"/>
24~ </OrderItems>
25~ </ProductBilling>")
26>
27> select * from dbo.ProductBilling
28> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
ProductBillingID ProductBillingXML
---------------- -------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
---------------------------------
1 <ProductBilling Billingnumber="1" customerid="22" orderdate="7/1/2005"><OrderItems><Item id="22" qty="1
" name="SQL"/><Item id="24" qty="1" name="T-SQL"/></OrderItems></ProductBilling>
2 <ProductBilling Billingnumber="1" customerid="40" orderdate="7/11/2005"><OrderItems><Item id="11" qty="
1" name="Notes"/></OrderItems></ProductBilling>
3 <ProductBilling Billingnumber="1" customerid="9" orderdate="7/22/2005"><OrderItems><Item id="11" qty="1
" name="SQL Notes"/><Item id="24" qty="1" name="T-SQL Notes"/></OrderItems></ProductBilling>
(3 rows affected)
1>
2> drop table dbo.ProductBilling
3> GO
Load XML data from a file to table
3> CREATE TABLE dbo.VisioDocs
4> (
5> id INT NOT NULL,
6> doc XML NOT NULL
7> );
8> GO
1>
2> INSERT INTO dbo.VisioDocs (id, doc)
3> SELECT 1, *
4> FROM OPENROWSET(BULK "C:\ORM.vdx",
5> SINGLE_BLOB) AS x;
6> INSERT INTO dbo.VisioDocs (id, doc)
7> SELECT 2, *
8> FROM OPENROWSET(BULK "C:\ER.vdx",
9> SINGLE_BLOB) AS x;
10> INSERT INTO dbo.VisioDocs (id, doc)
11> SELECT 3, *
12> FROM OPENROWSET(BULK "C:\UML.vdx",
13> SINGLE_BLOB) AS x;
14> INSERT INTO dbo.VisioDocs (id, doc)
15> SELECT 4, *
16> FROM OPENROWSET(BULK "C:\ER.vdx",
17> SINGLE_BLOB) AS x;
18> GO
Msg 4860, Level 16, State 1, Server J\SQLEXPRESS, Line 2
Cannot bulk load. The file "C:\ORM.vdx" does not exist.
1> SELECT doc.query("
2~ declare namespace VI="http://schemas.microsoft.ru/visio/2003/core";
3~ for $v in /VI:VisioDocument/VI:DocumentProperties
4~ return element Person
5~ {
6~ attribute creatorname
7~ {$v/VI:Creator[1]/text()[1]}
8~ }")
9> FROM dbo.VisioDocs;
10> GO
Msg 1934, Level 16, State 1, Server J\SQLEXPRESS, Line 1
SELECT failed because the following SET options have incorrect settings: "QUOTED_IDENTIFIER". Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or q
uery notifications and/or xml data type methods.
1>
2>
3> drop table VisioDocs;
4> GO
Modifying XML Data
3>
4> CREATE TABLE dbo.ProductBilling
5> (ProductBillingID int IDENTITY(1,1) PRIMARY KEY,
6> ProductBillingXML XML NOT NULL)
7> GO
1>
2> INSERT dbo.ProductBilling(ProductBillingXML)
3> VALUES ("<ProductBilling Billingnumber="1" customerid="22" orderdate="7/1/2005">
4~ <OrderItems>
5~ <Item id="22" qty="1" name="SQL"/>
6~ <Item id="24" qty="1" name="T-SQL"/>
7~ </OrderItems>
8~ </ProductBilling>")
9> GO
(1 rows affected)
1>
2> INSERT dbo.ProductBilling
3> (ProductBillingXML)
4> VALUES ("<ProductBilling Billingnumber="1" customerid="40" orderdate="7/11/2005">
5~ <OrderItems>
6~ <Item id="11" qty="1" name="Notes"/>
7~ </OrderItems>
8~ </ProductBilling>")
9> GO
(1 rows affected)
1>
2> INSERT dbo.ProductBilling
3> (ProductBillingXML)
4> VALUES ("<ProductBilling Billingnumber="1" customerid="9" orderdate="7/22/2005">
5~ <OrderItems>
6~ <Item id="11" qty="1" name="SQL Notes"/>
7~ <Item id="24" qty="1" name="T-SQL Notes"/>
8~ </OrderItems>
9~ </ProductBilling>")
10> GO
(1 rows affected)
1>
2> set quoted_identifier off
3>
4> UPDATE dbo.ProductBilling
5> SET ProductBillingXML.modify
6> ("insert <Item id="920" qty="1" name="SQL Server 2005 T-SQL Recipes"/>into (/ProductBilling/OrderItems)[1]")
7> WHERE ProductBillingID = 2
8>
9> set quoted_identifier on
10>
11> drop table dbo.ProductBilling
12> GO
(1 rows affected)
XML type column
4> Create table dbo.Eq2(
5> EqId int,
6> EqCompList xml)
7> GO
1>
2>
3> drop table dbo.Eq2 ;
4> GO