SQL Server/T-SQL Tutorial/XML/XML

Материал из SQL эксперт
Перейти к: навигация, поиск

Create index on XML column

   <source lang="sql">

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</source>


Creating xml Data Type Columns and insert data

   <source lang="sql">

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></source>


Insert XML data to a XML type column

   <source lang="sql">

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</source>


Insert XML data to database

   <source lang="sql">

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</source>


Load XML data from a file to table

   <source lang="sql">

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</source>


Modifying XML Data

   <source lang="sql">

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)</source>


XML type column

   <source lang="sql">

4> Create table dbo.Eq2( 5> EqId int, 6> EqCompList xml) 7> GO 1> 2> 3> drop table dbo.Eq2 ; 4> GO</source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/XML/XML&oldid=6377»