SQL Server/T-SQL Tutorial/XML/XML query
Содержание
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