Предложение WITH предоставляет формат набора строк (и дополнительные сведения о сопоставлениях, если необходимо), используя либо аргументSchemaDeclaration, либо указывая существующее значение аргумента TableName. Если необязательное предложение WITH не задано, результаты возвращаются в формате краевой таблицы. Краевые таблицы представляют собой структуру мелкогранулированного XML-документа (имена элементов/атрибутов, иерархия документа, пространства имен, и т. д.) в одной таблице.
Примеры
A. Использование простой инструкции SELECT с OPENXML
В следующем примере создается внутреннее представление образа XML с помощью процедуры sp_xml_preparedocument. Инструкция SELECT, которая использует поставщик набора строк OPENXML, выполняется для внутреннего представления XML-документа.
Значение аргумента flag устанавливается в 1. Это свидетельствует об атрибутивной модели сопоставления. Поэтому, XML-атрибуты сопоставляются столбцам набора строк. Аргумент rowpattern, заданный как /ROOT/Customer, указывает, какие узлы <Customers> должны обрабатываться.
Необязательный аргумент ColPattern (шаблон столбцов) не задан, так как имя столбца совпадает с именами XML-атрибутов.
Поставщик набора строк OPENXML создает набор строк с двумя столбцами (CustomerID и ContactName), из которых инструкция SELECT извлекает необходимые столбцы (в данном случае, все столбцы).
DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/> </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/> </Order></Customer></ROOT>'--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement that uses the OPENXML rowset provider.SELECT *FROM OPENXML (@idoc, '/ROOT/Customer',1) WITH (CustomerID varchar(10), ContactName varchar(20))
Ниже приводится результирующий набор.
CustomerID ContactName ---------- -------------------- VINET Paul HenriotLILAS Carlos Gonzlez
Если выполняется та же инструкция SELECT с аргументом flags, установленным в значение 2 и указывающим на сопоставление с использованием элементов, значения аргументов CustomerID и ContactName для обоих клиентов в XML-документе возвращается как NULL, потому что элементы<Customers> не содержат вложенных элементов.
Б. Указание ColPattern для сопоставления столбцов XML-атрибутам
Следующий запрос возвращает идентификатор клиента, дату заказа, идентификатор продукта и количественные атрибуты XML-документа. Аргументrowpattern задает элементы <OrderDetails>. ProductID и Quantity являются атрибутами элемента <OrderDetails>. Однако OrderID, CustomerID и OrderDateявляются атрибутами родительского элемента (<Orders>).
Необязательный аргумент ColPattern задан. Это указывает на следующее.
· OrderID, CustomerID и OrderDate в наборе строк сопоставляются атрибутам родителя узлов, заданных аргументом rowpattern в XML-документе.
· Столбец ProdID в наборе строк сопоставляется атрибуту ProductID, и столбец Qty в наборе строк сопоставляется атрибуту Quantity узлов, заданных в аргументе rowpattern.
Хотя сопоставление с использованием элементов задано в аргументе flags, сопоставление, заданное в аргументе ColPattern, переписывает это сопоставление.
DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> <OrderDetail ProductID="11" Quantity="12"/> <OrderDetail ProductID="42" Quantity="10"/> </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> <OrderDetail ProductID="72" Quantity="3"/> </Order></Customer></ROOT>'--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- SELECT stmt using OPENXML rowset providerSELECT *FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2) WITH (OrderID int '../@OrderID', CustomerID varchar(10) '../@CustomerID', OrderDate datetime '../@OrderDate', ProdID int '@ProductID', Qty int '@Quantity')
Тип данных xmlявляется родным типом данных для хранения XML документов или фрагментов в столбце, локальной переменной, или параметре до 2 гигабайтов. Способность к хранению XML непосредственно в реляционной базе данных имеет много преимуществ для разработчиков приложений.
Преимущества хранения XML в родном формате
Преимущества хранения XML включают:
■ И структурированные, и полуструктурированные данные хранятся в одном месте, облегчая управление ими.
■ Вы можете определить содержимое переменной в реляционной модели.
■ Вы можете выбрать самую подходящую модель данных для обеспечения специфических требований Вашего приложения, используя высоко оптимизированное хранилище данных и окружение запросов.
Функциональные возможности XML
Тип данных SQL Server 2005 xmlхранит информационный набор документа XML в эффективном внутреннем формате. Данные можно рассматривать как оригинальный документ XML за исключением того, что не хранятся незначащие пробелы, порядок атрибутов, префиксы пространств имен и объявления XML. SQL Server 2005 обеспечивает следующие функциональные возможности для типа данныхxml:
Индексирование XML. Столбцы, определенные как xml, могут быть проиндексированы при использовании индексов XML и полнотекстовые индексы. Это может значительно увеличить производительность запросов, которые получают данные XML.
Методы получения данных на основе запросов XQuery. Тип данных xmlподдерживает методыquery, value, и exist. Они могут использоваться, чтобы извлечь данные из XML при использовании выражений XQuery.
Модификация данных на основе запросов XQuery. Тип данных xml поддерживает метод modify, который использует расширение спецификации XQuery, чтобы выполнить обновления данных XML.
Типизированный XML. Это XML, который связан с некоторой схемой XML. Схема определяет элементы и атрибуты, которые разрешены в документе XML этого типа и определяет пространство имен для них. Когда используетсятип данныхxml, чтобы хранить типизированный XML, SQL Server проверяет соответствие XML-документа его схеме и оптимизирует внутреннее хранение данных, назначая соответствующие типы данных SQL Server тем данным, которые основаны на типах данных XML, определенных в схеме.
Дополнительная информация
Дополнительную информацию об XML InfoSet, см. в “XML Information Set” спецификация W3C.
Дополнительную информацию о типе данныхxml, см. в “xml Data Type” в SQL Server Books Online.
Что такое XQuery?
Введение
XQuery используется, чтобы формировать запросы к данным XML. Синтаксис XQuery включает и расширяет выражения XPath 2.0 и позволяют выполнить сложные запросы к данным XML. Тип данных xmlв SQL Server поддерживает методы, с помощью которых данные в xmlможут быть получены или обновлены посредством выражения XQuery.
Поддержка XQuery в SQL Server 2005 основана на рабочем проекте W3C языковой спецификации XQuery 1.0 (доступного на Вебсайте W3C), и поэтому могут быть незначительные несоответствия с выпущенной спецификацией.
Синтаксис XQuery
Запрос XQuery состоит из двух главных секций: необязательная секция Prolog, в которой могут быть объявлены пространства имен и импортированы схемы; и секция body (тело), в которой используются выражения XQuery, чтобы определить данные, которые будут получены. Выражением XQuery может быть простой путь, который описывает узлы XML, которые будут получены, или сложное выражение, которое генерирует результат в формате XML.
Путь XQuery основан на языке XPath и описывает местоположение узла в документе XML. Пути могут быть абсолютными (описание местоположения узла в дереве XML от корневого элемента) или относительными (описание местоположения узла относительно ранее идентифицированного узла). Примеры в следующей таблице показывают некоторые простые пути XQuery.
Примеры путей
Описание
/InvoiceList/Invoice
Все элементы Invoiceнепосредственно содержатся в корневом элементе InvoiceList
(/invoicelist/invoice) [2]
второй элемент Invoiceв корневом элементе InvoiceList
(InvoiceList/Invoice/@InvoiceNo) [1]
Атрибут InvoiceNo первого элемента Invoiceв корневом элементе InvoiceList
(InvoiceList/Invoice/Customer/text()) [1]
Текст первого элемента Customer в элементе Invoiceв корневом элементе InvoiceList
/InvoiceList/Invoice [@InvoiceNo=1000]
Все элементы Invoiceв элементе InvoiceList, которые имеют атрибут InvoiceNo с значением 1000
Операторы FLOWR
Языковая спецификация XQuery включает операторыfor, let, order by, where, и return, обычно известные как FLOWR (произносится "flower" - цветок) операторы. SQL Server 2005 поддерживает операторы for, where, и return, которые описаны в следующей таблице.
Оператор
Описание
FOR
Используется, чтобы повторить операцию через группу узлов на одном и том же уровне в XML документе.
WHERE
Используется применять фильтры к узловым итерациям. XQuery включает функции, такие как COUNT, которые могут использоваться с WHERE.
RETURN
Используется, чтобы определить, что XML возвратился изнутри повторения.
Следующий пример показывает выражение XQuery, которое включает ключевые словаFOR, WHERE, и RETURN.
for $i in /InvoiceList/Invoice
where count($i/Items/Item) > 1
return $i
Этот пример возвращает каждый элемент Invoice, который включает больше чем один элемент Itemв его дочернем элементе Items.
Работа с пространствами имен
Если запрашиваемый XML содержит пространство имен, то XQuery может включать объявление пространства имен в Прологе запроса, используя следующий синтаксис.
xml.method('declare default element namespace "http://namespace";
method body')
Дополнительная информацияДополнительную информацию об использовании пространства имен с XQuery, см. “XQuery Basics” в SQL Server Books Online.
Методы query, value, и exist
Введение
Тип данных SQL Server 2005 xmlподдерживате четыре метода, которые могут использоваться, чтобы запрашивать или изменять данные XML. Эти методы вызываются при использовании синтаксиса data_type.method_name знакомый большинству разработчиков. Понимание цели каждого из методов поможет Вам создать приложения, которые обрабатывают XML непосредственно в базе данных.
Метод query
метод queryиспользуется, чтобы извлечь XML-данные из типа данныхxml. XML, полученный методом query, определяется по выражению XQuery, которое передается как параметр.
Следующий пример показывает, как использовать метод query.
SELECT xmlCol.query('declare default element namespace
"http://schemas.adventure-works.com/InvoiceList";
<InvoiceNumbers>
{
for $i in /InvoiceList/Invoice
return <InvoiceNo>
{number($i/@InvoiceNo)}
</InvoiceNo>
}
</InvoiceNumbers>')
Метод value
Метод valueиспользуется, чтобы возвратить единственное значение из документа XML. Использовать метод value, Вы должны определить выражение XQuery, которое идентифицирует единственный узел в запрашиваемом XML и тип данных Transact-SQL того значения, которое будет возвращено. Следующий пример показывает, как использовать метод value.
SELECT xmlCol.value('declare default element namespace
"http://schemas.adventure-works.com/InvoiceList";
/InvoiceList/Invoice/@InvoiceNo)[1]', 'int')
Метод exist
Метод exist используется, чтобы определить, существует ли указанный узел в XML документе. Метод exist возвращает 1, если существует один или более экземпляров указанного узла в документе, и возвращает 0, если узел не существует. Следующий пример показывает как использовать метод exist.
SELECT xmlCol.exist('declare default element namespace
"http://schemas.adventure-works.com/InvoiceList";
/InvoiceList/Invoice[@InvoiceNo=1000]')
Связывание реляционных столбцов и переменных
SQL Server 2005 поддерживает специфические для Microsoft расширения языка XQuery, позволяющие ссылаться на реляционные столбцы или переменные. Это называется связывание реляционного столбца или переменная. Когда оператор SELECT, который получает данные из таблицы, включает xmlметод, чтобы получить XML из xmlколонки, может использоваться функция sql:column, чтобы включать значения не xmlстолбца в XML-данных, как показано в следующем примере.
SELECT StoreName, Invoices.query('declare default element namespace
"http://schemas.adventure-works.com/InvoiceList";
<Invoices>
<Store>{sql:column("StoreName")}</Store>
{
for $i in /InvoiceList/Invoice
return $i
}
</Invoices>') InvoicesWithStoreName
FROM Stores
Точно так же может использоваться sql:variable, чтобы сослаться на переменную в хранимой процедуре, как показано в следующем примере.
SELECT Invoices.query('declare default element namespace
"http://schemas.adventure-works.com/InvoiceList";
<Invoices>
{
for $i in /InvoiceList/Invoice
where $i/@InvoiceNo = sql:variable("@invoiceNo")
return $i
}
</Invoices>')
FROM #Stores
WHERE StoreID=@store
Метод modify
Введение
Вы можете использовать метод modify, чтобы обновить данные XML в типе данныхxml. Mетод modifyиспользует три расширения для языковой спецификации XQuery: insert, replace, и delete. Эти расширения упоминаются как XML DML.
Оператор insert
Вы можете использовать оператор insert, чтобы добавить узлы к XML в столбце или переменнойxml. У оператора insert следующий синтаксис.
insert Expression1 (
{as first | as last} into | after | before
Expression2 )
Параметры синтаксиса для ключевого слова insertописаны в следующей таблице.
Параметр
Описание
Expression1
узел, который будет вставлен. Это может быть литерал XML-например, <Item Product="5" Quantity="1"/>Это может также быть выражение element, чтобы вставить текстовый узел — например, element SalesPerson { "Alice" }Наконец, это может быть выражение attribute, чтобы вставить атрибут — например, attribute discount{"1.50"}. .
as first
Используется, чтобы вставить новый XML как первый элемент иерархии.
as last
Используется, чтобы вставить новый XML как последний элемент в иерархии.
into
Используется, чтобы вставить Expression1 в Expression2.
after
Используется, чтобы вставить Expression1 после Expression2.
before
Используется, чтобы вставить Expression1 перед Expression2.
Expression2
выражение XQuery, которое идентифицирует существующий узел в документе.
Следующий пример показывает, как использовать оператор insertXQuery в методеinsert.
SET @xmlDoc.modify('declare default element namespace
"http://schemas.adventure-works.com/InvoiceList";
insert element salesperson {"Alice"}
as first
into (/InvoiceList/Invoice)[1]')
Оператор replace
Вы можете использовать операторreplace, чтобы обновить значение xml. Операторreplaceимеет следующий синтаксис.
replace value of Expression1 with Expression2
Параметры синтаксиса replaceописаны в следующей таблице.
Параметр
Описание
Expression1
выражение XQuery, идентифицирующее узел, содержащий значение для замены
Expression2
новое значение узла.
Следующий пример показывает, как использовать операторreplace в методе modify.
SET xmlCol.modify('declare default element namespace
"http://schemas.adventure-works.com/InvoiceList";
replace value of
(/InvoiceList/Invoice/SalesPerson/text())[1]
with "Holly"')
Оператор delete
Вы можете использовать оператор delete, чтобы удалить узел из значения xml. У оператора delete следующий синтаксис.
delete Expression
Параметр Expression - выражение XQuery, идентифицирующее узел, который будет удален.
Следующий пример показывает, как использовать оператор delete в методе modify.
SET xmlCol.modify('declare default element namespace