SQL Server/T-SQL Tutorial/XML/XML query

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

Retrieve XML data using XPath

   <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> 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> set quoted_identifier on 28> 29> SELECT ProductBillingID 30> FROM dbo.ProductBilling 31> WHERE ProductBillingXML.exist 32> ("/ProductBilling/OrderItems/Item[@id=11]") = 1 33> GO (1 rows affected) (1 rows affected) (1 rows affected) ProductBillingID 

              2
              3

(2 rows affected) 1> 2> set quoted_identifier off 3> 4> drop table dbo.ProductBilling 5> GO</source>


SELECT @ProductBillingXML.query("/ProductBilling/OrderItems/Item")

   <source lang="sql">

4> 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> 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> 11> GO (1 rows affected) 1> set quoted_identifier on 2> SELECT ProductBillingXML.query("/ProductBilling/OrderItems/Item") from dbo.ProductBilling 3> GO

<Item id="22" qty="1" name="SQL"/><Item id="24" qty="1" name="T-SQL"/>

<Item id="11" qty="1" name="Notes"/>

<Item id="11" qty="1" name="SQL Notes"/><Item id="24" qty="1" name="T-SQL Notes"/>

(3 rows affected) 1> 2> set quoted_identifier off 3> 4> drop table dbo.ProductBilling 5> GO</source>


XML query on XML column

   <source lang="sql">

4> CREATE TABLE dbo.VisioDocs 5> ( 6> id INT NOT NULL, 7> doc XML NOT NULL 8> ); 9> 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 id, 2> doc.value("declare namespace VI= 3~ "http://schemas.microsoft.ru/visio/2003/core"; 4~ (/VI:VisioDocument/VI:DocumentProperties/VI:Company)[1]", 5> "NVARCHAR(50)") AS company 6> FROM dbo.VisioDocs; 7> 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>


XPath query on XML

   <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> INSERT INTO dbo.Contacts VALUES(1, N"Mike", 1, N"<I18N xmlns="I18N"><ID>0</ID><FL>Spanish</FL></I18N>"); 3> INSERT INTO dbo.Contacts VALUES(2, N"Her", 0, N"<Foreigns xmlns="Foreign"><NL>German</NL><ENG>1</ENG></Foreigns>"); 4> INSERT INTO dbo.Contacts VALUES(3, N"Ric", 1, N"<I18N xmlns="I18N"><ID>1</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> SELECT contactid, contactname, 3> otherattributes.query(" 4~ declare namespace D="I18N"; 5~ /D:I18N/D:FL/text()") AS languagespoken 6> FROM dbo.Contacts 7> WHERE I18N = CAST(1 AS BIT); 8> GO Msg 1934, Level 16, State 1, Server J\SQLEXPRESS, Line 2 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> drop table dbo.Contacts;</source>


XPath search and element index

   <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> INSERT dbo.ProductBilling 2> (ProductBillingXML) 3> VALUES ("<ProductBilling Billingnumber="1" customerid="40" orderdate="7/11/2005"> 4~ <OrderItems> 5~ <Item id="11" qty="1" name="Notes"/> 6~ </OrderItems> 7~ </ProductBilling>") 8> GO (1 rows affected) 1> INSERT dbo.ProductBilling 2> (ProductBillingXML) 3> VALUES ("<ProductBilling Billingnumber="1" customerid="9" orderdate="7/22/2005"> 4~ <OrderItems> 5~ <Item id="11" qty="1" name="SQL Notes"/> 6~ <Item id="24" qty="1" name="T-SQL Notes"/> 7~ </OrderItems> 8~ </ProductBilling>") 9> GO (1 rows affected) 1> 2> set quoted_identifier on 3> 4> SELECT DISTINCT 5> ProductBillingXML.value 6> ("(/ProductBilling/OrderItems/Item/@name)[1]", "varchar(30)") as BookTitles 7> FROM dbo.ProductBilling 8> UNION 9> SELECT DISTINCT 10> ProductBillingXML.value 11> ("(/ProductBilling/OrderItems/Item/@name)[2]", "varchar(30)") 12> FROM dbo.ProductBilling 13> GO BookTitles

NULL Notes SQL SQL Notes T-SQL T-SQL Notes (6 rows affected) 1> 2> set quoted_identifier off 3> 4> drop table dbo.ProductBilling 5> GO</source>

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