XML query on XML column : XML query « XML « SQL Server / T-SQL Tutorial






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.com/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








24.4.XML query
24.4.1.XML query on XML column
24.4.2.XPath query on XML
24.4.3.SELECT @ProductBillingXML.query('/ProductBilling/OrderItems/Item')
24.4.4.Retrieve XML data using XPath
24.4.5.XPath search and element index