SQL Server/T-SQL Tutorial/XML/XML query — различия между версиями

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

Текущая версия на 10:22, 26 мая 2010

Retrieve XML data using XPath

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


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

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


XML query on XML column

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


XPath query on XML

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;


XPath search and element index

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