Работа с XML в SQL Server (часть 2) (ru-RU). T sql работа с xml

Работа с XML данными в Microsoft SQL Server 2008. Тип данных «xml».

В прошлой статье мы узнали, каким образом из базы можно извлекать реляционные данные в виде XML структуры. Сегодня мы познакомимся с принципами хранения структуры XML данных в базе.

Основной элемент поддержки XML в MS SQL Server – тип данных “xml”. Данный тип может использоваться для объявления переменных и как тип колонки в таблице наравне со стандартными типами данных SQL (int, nvarchar и т.д.).

Для типа данных XML доступны следующие специализированные операции. Приведем их краткое описание:

query() – осуществляет запросы к XML данным;
nodes() – извлекает поддерево из структуры XML.
value() – позволяет извлекать значения атрибутов из XML элемента;
exist() – проверяет существуют ли результаты запроса;
modify() – производит обновление XML данных;

Рассмотрим данные команды подробнее:

Команда «query()».

Эта команда позволяет писать запросы к XML дереву. Команда возвращает набор результатов, соответствующих запросу. Рассмотрим пример. Допустим, у нас есть следующая XML структура:

1: DECLARE @xmlData XML =N' 2: <Shops> 3: <Shop> 4: <device name="Sensation" vendor="HTC" /> 5: <device name="iPhone" vendor="Apple" /> 6: </Shop> 7: <Shop> 8: <device name="Mozart" vendor="HTC" /> 9: <device name="Lumia" vendor="Nokia" /> 10: </Shop> 11: </Shops>'; Выполним следующий запрос: 1: SELECT @xmlData.query('/Shops/Shop/device') Как результат, мы получим все элементы , путь к которым соответствует нашему запросу: 1: <device name="Sensation" vendor="HTC" /> 2: <device name="iPhone" vendor="Apple" /> 3: <device name="Mozart" vendor="HTC" /> 4: <device name="Lumia" vendor="Nokia" />

Стоит отметить, что имена элементов и атрибутов регистрозависимые, как в XML структуре, так и в тексте запроса.

Также, в запросе, можно наложить некоторые ограничения на выбираемые данные, помимо пути. Например, давайте напишем запрос, который будет выбирать из структуры XML все устройства, производителем которых является компания «HTC»:

1: SELECT @xmlData.query('/Shops/Shop/device/.[@vendor cast as xs:string? = "HTC"]') as Data Запрос вернет следующий результат: Команда «nodes()».

Разбивает XML структуру на одно или несколько поддеревьев, в соответствии с указанным запросом. Для примера будем использовать ту же структуру данных @xmlData.

Выполним следующий запрос: 1: SELECT shop.query('.') as data FROM @xmlData.nodes('/Shops/Shop') col(shop)

Данный запрос разобьет исходную структуру на строки, по количеству элементов <Shop> и вернет нам две строки:

Разберем текст запроса подробнее.

‘@xmlData.nodes('/Shops/Shop')’ - собственно разбивает данные по указанному запросу.

‘col(shop)’ – это псевдоним для результатов разбиения. Данный псевдоним необходим для дальнейшей работы с результатами.

‘shop.query('.')’ – здесь осуществляется запрос к каждой строке результатов, при помощи псевдонима. Данный подзапрос не осуществляет фильтрации, выбирая все данные из поддерева.

Команда «value()».

С помощью данной команды можно извлекать значения из XML дерева. Команда умеет конвертировать строковые литералы из XML текста в любые типы данных среды MS SQL Server.

В качестве примера давайте преобразуем XML структуру @xmlData в табличное представление, выполнив следующий запрос:

2: device.value('@id', 'int') as Id, 3: device.value('@vendor', 'nvarchar(50)') as Company, 4: device.value('@name', 'nvarchar(50)') as Name 5: FROM @xmlData.nodes('/Shops/Shop/device') col(device) Данный запрос вернет следующий табличный результат:

Команда value() принимает два параметра: название атрибута с префиксом @ и название типа данных, к которому необходимо привести переменную.

Аналогичным способом можно получить доступ к значению, расположенным внутри элемента хмл, например:

1: DECLARE @data XML = N' 3: <Shop>Winodws Marketplace</Shop> 7: shop.value('@id', 'int') as Id, 8: shop.value('.[1]', 'nvarchar(50)') as CompanyName 9: FROM @data.nodes('/Shops/Shop') col(shop) Результат будет следующим:

Также можно получить доступ к родительскому элементу любого уровня. Давайте модернизируем наш первый запрос к @xmlData, добавив в выборку Id магазина, в котором находится устройство:

2: device.value('@id', 'int') as Id, 3: device.value('@vendor', 'nvarchar(50)') as Company, 4: device.value('@name', 'nvarchar(50)') as Name, 5: device.value('../@id', 'int') as ShopId 6: FROM @xmlData.nodes('/Shops/Shop/device') col(device) Результат похож на то, что мы хотели:

Результат отобразился как надо, однако при больших объемах данных такой запрос будет крайне медленным и неэффективным. Проблема в том, что для каждого устройства среда выполнения запроса ищет родительский элемент и считывает его атрибут. Перепишем запрос следующим образом:

2: device.value('@id', 'int') as Id, 3: device.value('@vendor', 'nvarchar(50)') as Company, 4: device.value('@name', 'nvarchar(50)') as Name, 5: shop.value('@id', 'int') as ShopId 7: @xmlData.nodes('/Shops/Shop') col(shop) 9: shop.nodes('device') tab(device)

Результат будет аналогичным, однако теперь мы поменяли логику запроса таким образом, что вначале будут выбираться все магазины, а потом, для каждого магазина, будут присоединяться устройства. Поскольку устройств всегда будет намного больше, чем магазинов, данный запрос даст нам существенный прирост производительности.

Команда «exist()».

Данная команда возвращает значения типа bit. 1 – если результаты, соответствующие запросу существуют, и 0 – если не существуют.

Запрос формируется таким же образом, как и в команде query(). Команда используется, как правило, для наложения ограничений в запросе выборки, применяя фильтрующий запрос exist() к поддереву. Приведем пример:

2: shop.value('@id', 'int') as ShopId 4: @xmlData.nodes('/Shops/Shop') col(shop) 5: WHERE shop.exist('device[@vendor cast as xs:string? = "Nokia"]') = 1

Данный запрос выберет Id всех магазинов, в которых присутствуют устройства от компании «Nokia»:

На этом данная статья подходит к концу. В следующей статье мы рассмотрим последнюю команду типа «xml» - modify(), а также обсудим различные способы оптимизации взаимодействия с XML структурами в среде Microsoft SQL Server.

djekmusic.blogspot.com

Работа с XML в SQL Server (часть 1) - Статьи TechNet - Россия (Pусский)

Поддержка XML впервые появилась в SQL Server 2000 - в язык T-SQL были добавлены ключевые слова FOR XML и OPENXML, которые позволяли разработчикам, соответственно, извлекать результаты запросов к базам данных в виде XML-потока и сохранять XML-документы в базе данных. Эти возможности были существенно расширены в SQL Server 2005 - был введен новый тип данных XML, поддерживающий проверку на уровне XSD_схемы, выполнение XQuery_операций и индексирование. В SQL Server 2008 возможности по работе с XML, как со встроенным типом данных, еще больше расширены. Начнем с того, что кратко вспомним ключевые возможности по работе с XML, реализованные в предыдущих версиях SQL Server - SQL Server 2000 и SQL Server 2005. Как я отметил выше, в SQL Server 2000 в язык T-SQL были добавлены ключевые слова FOR XML и OPENXML. FOR XML - это атрибут команды SELECT, указывающий на то, что результаты выполнения запроса должны быть представлены в виде XML-потока. Пример использования данной функциональности показан ниже. Следующий запрос:

SELECT ProductID, ProductName

FROM Products Product

FOR XML AUTO

вернет следующий XML-документ:

Функция OPENXML предназначена для выполнения обратных действий - создания записи на основе переданного ей XML-документа. Пример использования данной функциональности показан ниже. Следующий запрос:

DECLARE @doc nvarchar(1000)

SET @doc = '<Order OrderID = "1011">

</Order>'

DECLARE @xmlDoc integer

EXEC sp_XML-preparedocument @xmlDoc OUTPUT, @doc

SELECT * FROM

OPENXML (@xmlDoc, 'Order/Item', 1)

WITH

(OrderID integer '../@OrderID',

ProductID integer,

Quantity integer)

EXEC sp_XML-removedocument @xmlDoc

приведет к созданию такой записи:

OrderID ProductID Quantity

1011 1 2

1011 2 1

Обратите внимание на использование хранимых процедур sp_XML-preparedocument и sp_XML-removedocument для создания XML-документа в памяти и его удаления после записи в базу данных.

В SQL Server 2005 атрибут FOR XML был расширен возможностью задания новых опций для корневых элементов и имен элементов документа, была включена поддержка вложенных вызовов запросов с FOR XML, позволяющих создавать сложные иерархии внутри XML-документов, а также новый режим PATH, позволяющий описать структуру получаемого XML-документа с помощью синтаксиса XPath. Пример использования данной функциональности показан ниже. Следующий запрос:

SELECT ProductID AS '@ProductID',

ProductName AS 'ProductName'

FROM Products

FOR XML PATH ('Product'), ROOT ('Products')

создаст такой XML-документ:

<ProductName>Widget</ProductName>

</Product>

<ProductName>Sprocket</ProductName>

</Product>

</Products>

Помимо расширений функциональности, впервые появившейся в SQL Server 2000, в SQL Server 2005 появился встроенный тип данных XML, использование которого позволяет создавать переменные и колонки для хранения XML-данных. Пример использования данной функциональности показан ниже.

CREATE TABLE SalesOrders

(OrderID integer PRIMARY KEY,

OrderDate datetime,

CustomerID integer,

OrderNotes xml)

Тип данных xml может использоваться для хранения в базе данных отформатированных документов (HTML, XML, XHTML и т. п.) или полуструктурированных данных. Можно хранить нетипизованные или типизованные XML-данные - последние могут быть проверены на соответствие XSD-схеме. Для задания схемы, используемой для проверки вводимых данных, используется команда СREATE XML SCHEMA COLLECTION:

CREATE XML SCHEMA COLLECTION ProductSchema AS

'<?xml version="1.0" encoding="UTF_16"?>

<!- Здесь располагается сама схема ->

</xs:schema>'

После того как схема описана, она ассоциируется с XML-переменной или колонкой соответствующего типа - пример показан ниже.

CREATE TABLE SalesOrders

(OrderID integer PRIMARY KEY,

OrderDate datetime,

CustomerID integer,

OrderNotes xml(ProductSchema))

Проверка типизованного XML на соответствие ассоциированной с переменной или колонкой соответствующего типа схеме происходит при записи или обновлении данных - эта возможность позволяет, например, гарантировать соответствие вводимых данных принятым стандартам или обеспечить совместимость с документами различных типов. Тип данных xml также поддерживает ряд методов, которые могут использоваться для выполнения запросов или манипуляции с XML-данными. Например, можно использовать метод query для выборки данных, как это показано в следующем примере:

declare @x xml

set @x=

'<Invoices>

<Customer>Kim Abercrombie</Customer>

<Items>

</Items>

</Invoice>

<Customer>Margaret Smith</Customer>

<Items>

</Items>

</Invoice>

</Invoices>'

SELECT @x.query(

'<CustomerList>

{

for $invoice in /Invoices/Invoice

return $invoice/Customer

}

</CustomerList>')

В приведенном выше запросе используется XQuery_синтаксис, с помощью которого ищутся все элементы Invoice, находящиеся в данном документе, и возвращается XML-документ, который содержит элемент Customer для каждого элемента Invoice - пример результирующего документа показан ниже.

<Customer>Kim Abercrombie</Customer>

<Customer>Margaret Smith</Customer>

</CustomerList>

Другая новинка, относящаяся к поддержке XML и появившаяся в SQL Server 2005 - это поддержка XML-индексов. Имеется возможность создания первичных и вторичных XML-индексов для колонок типа xml, что позволяет повысить производительность запросов к XML-данным. Первичный XML-индекс - это сжатое представление все ветвей XML-документа, которые процессор обработки запросов использует для быстрого нахождения ветвей в документе. После того как первичный XML-индекс создан, можно создать вторичный XML-индекс, который поможет повысить производительность при выполнении ряда специфических запросов. В следующем примере показано, как создать первичный XML-индекс и вторичный XML-индекс типа PATH, который позволит улучшить производительность при выполнении XPath_ запросов к данному XML-документу.

CREATE PRIMARY XML INDEX idx_XML-Notes

ON SalesOrders (Notes)

CREATE XML INDEX idx_XML-Path_Notes

ON SalesOrders (Notes)

USING XML INDEX idx_XML-Notes

FOR PATH

Функциональность, реализованная в SQL Server 2000 и 2005, была расширена в SQL Server 2008. К ключевым расширениям в области поддержки работы с XML в SQL Server 2008 можно отнести улучшенные возможности проверки данных на соответствие схеме, расширенную поддержку XQuery и расширенную функциональность при вставке XML-данных средствами DML (Data Manipulation Language).

Расширения XSD Проверка данных на соответствие схеме позволяет убедиться в том, что XML-документ, хранимый в SQL Server, соответствует определенному стандарту и заданным на уровне схемы бизнес_правилам. На уровне схемы задаются допустимые в XML-документе элементы и атрибуты, что позволяет убедиться в том, что XML-документ содержит требуемые данные в рамках предопределенной структуры. В SQL Server 2005 появилась поддержка проверки XML-данных на основе коллекций XSD_ схем. Подход заключается в том, что вы создаете коллекцию схем, которая содержит схемы с правилами для XML-данных, используя команду CREATE XML SCHEMA COLLECTION, а затем ссылаетесь на имя коллекции при задании колонки или переменной типа xml, которая должна соответствовать правилам, описанным на уровне схемы. SQL Server выполняет проверку вводимых или обновляемых данных на соответствие указанной коллекции схем. В SQL Server 2005 реализовано подмножество полной спецификации XML Schema и поддерживаются ключевые сценарии проверки вводимых XML-данных. В SQL Server 2008 поддержка XSD_схем расширена за счет введения дополнительных возможностей, к которым относятся поддержка проверки на уровне any (т. н. lax validation), полная поддержка проверки на уровне dateTime, time и date, включая сохранение информации о часовых поясах и улучшенная поддержка типов union и list. Поддержка проверки на уровне шаблонов реализована на уровне конструкций any, anyAttribute и anyType. Например, следующая схема:

<xs:complexType name="Order" mixed="true">

<xs:sequence>

<xs:element name="CustomerName"/>

<xs:element name="OrderTotal"/>

<xs:any namespace="##other" processContents="skip" minOccurs="0" maxOccurs="unbounded"/>

</xs:sequence>

</xs:complexType>

задает XML-элемент с именем Order, который должен содержать вложенные элементы с именами CustomerName и OrderTotal. Помимо этого, элемент может содержать неограниченное число других элементов, относящихся к пространствам имен, отличным от того, в котором определен тип Order. Следующий XML-документ содержит экземпляр элемента Order, соответствующий описанию схемы. Обратите внимание на то, что в документе также содержится элемент shp:Delivery, который не описан в схеме.

<Order>

<CustomerName>Graeme Malcolm</CustomerName>

<shp:Delivery>Express</shp:Delivery>

</Order>

</Invoice>

Проверка на соответствие шаблону зависит от атрибута processContents для секции схемы, в которой описываются шаблоны. В SQL Server 2005 схемы могут содержать значения атрибута processContents - skip и strict для объявлений any и anyAttribute. В предыдущем примере атрибут processContents для шаблона имел значение skip - таким образом, содержимое данного элемента не проверялось. Даже если в схеме описан элемент shp:Delivery, он не будет проверяться до тех пор, пока в описании шаблона для элемента Order значение атрибута processContents не будет установлено в strict.

В SQL Server 2008 добавлено третье возможное значение атрибута processContents - lax, которое позволяет указать на то, что все элементы, описанные в схеме, должны быть включены в проверку, а элементы, не содержащиеся в схеме, могут быть проигнорированы. Таким образом, если в предыдущем примере присвоить атрибуту processContents для шаблона значение lax, и добавить в схему описание элемента shp:Delivery, этот элемент будет включен в проверку. Но так как элемент shp:Delivery не описан в схеме, он не будет включен в проверку. Помимо этого, спецификация XML Schema определяет, что атрибут anyType автоматически подлежит проверке согласно описанным выше правилам. В SQL Server 2005 lax-проверка не поддерживалась - по умолчанию выполнялась проверка на уровне strict. Для задания данных, описывающих дату и время, в XML-схемах используется тип данных dateTime. Дата и время задаются в следующем формате: 2007 12 01T21:11:20:000Z, который представляет собой 1-е декабря 2007 года, 11 часов 20 минут по Гринвичу - UTC (000Z). Другие часовые пояса в следующем формате: 000+3:00, например, описывает московское время. Спецификация XML Schema задает компонент часового пояса типов данных dateTime, date и time как опциональный. Тем не менее, в SQL Server 2005 требовалось указание часового пояса при задании данных типа date_Time, date и time. Помимо этого, в SQL Server 2005 данные о часовом поясе не сохранялись, а приводились к UTC - например, значение 2007_12_25T06:00:00:000_8:00 превращалось в 2007_12_25T14:00:00:000Z. В SQL Server 2008 эти ограничения удалены - при задании даты и времени можно не указывать часовой пояс, но если он указан, данные сохраняются корректно. Разработчики могут использовать XML-схемы для задания типов данных для XML-данных так, что эти данные могут содержать набор значений, присваиваемых элементам и атрибутам. Например, можно определить тип sizeListType, который ограничивает список возможных значений, присваиваемых элементу AvaliableSizes до S, M и L. В SQL Server 2005 поддерживаются схемы, содержащие простые определения типов и соответствующие ограничения. Например, можно использовать тип list для задания возможных размеров, как показано в следующем примере:

<xs:simpleType name="sizeListType">

<xs:list>

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:enumeration value="S"/>

<xs:enumeration value="M"/>

<xs:enumeration value="L"/>

</xs:restriction>

</xs:simpleType>

</xs:list>

</xs:simpleType>

Такое описание схемы позволяет создать элемент, который содержит все возможные размеры в виде списка значений, разделенных пробелами - это показано в следующем примере:

social.technet.microsoft.com

Практикум 1. Работа с XML

Поддержка XML данных была заявлена, и частично реализована еще в версии MS SQL Server 2000, но имела ряд существенных недостатков, из-за которых активное использование этого формата данных было затруднительно. Многие из вас знали о дополнительных возможностях оператора SELECT … FOR XML, позволяющих возвращать выборку в формате XML. Но несмотря на довольно большой набор опций форматирования использовать эту конструкцию не удавалось слишком часто из-за того, что конструкция FOR XML не допускалась для использования в подзапросах. Это и многие другие ограничения, связанные с XML данными отсутствуют в новой версии СУБД. MS SQL Server 2005 позволяет создавать переменные типа xml, использовать xml для описания типа столбцов таблиц, связывать с xml данными XSD схемы, индексировать xml данные.

Встроенная поддержка XML в СУБД MS SQL Server 2005 позволяет:

Использовать тип данных xml для определения переменных, столбцов таблиц
Определять XSD схемы для автоматической проверки xml данных на структурную корректность
Извлекать данные в формате xml с помощью улучшенных инструкций FOR XML и OPENXML
Извлечение и модификация xml данные с помощью технологии XQuery
Индексировать xml данные

Подробно о встроенной поддержке xml в СУБД SQL Server 2005 вы можете узнать на наших курсахMOC-2779. Реализация баз данных Microsoft SQL Server 2005 и MOC-2734. Обновление навыков разработки баз данных до Microsoft SQL Server 2005

В рамках данного практикума мы обсудим следующие темы:

Тема 1. Тип данных xml

Встроенный тип данных xml позволяет сохранять XML документы или фрагменты XML данных в переменных, столбцах таблиц и передавать их в качестве параметров. Максимальный объем хранимой информации – 2 Гб. Вы можете связывать с переменными, столбцами и параметрами xml типа коллекцию XSD схем, позволяющих проверять XML данные на соответствие ожидаемой структуре. Проверка на синтаксическую корректность XML данных происходит автоматически и никаких дополнительных настроек не требует. Т.е. допускается хранение и обработка только well-formed XML данных.

Листинг 1.1 Работа с переменными типа xml

declare @dxml xml declare @str nvarchar(200) declare @xmlItem xml set @dxml='<description><education><item>FPS</item>' +'</education&gt</description>' set @str='<items><item>Value</item&gt</items>' set @xmlItem=@str 'или set @xmlItem=Convert(xml, @str) create table T1( id int not null primary key, name varchar(100), data xml ) insert into T1 values(1,'Petrov',@dxml)

Тема 2. Типизированные xml данные

При формировании xml документов необходимо делать их well-formed, или синтаксически корректными. Только well-formed документы могут быть обработаны встроенными xml процессорами, и сохранены в ячейках таблиц и переменных. Кроме того, довольно часто требуется структурная корректность документа, соответствие набора его элементов некоторому шаблону. Xml документ является Valid документом, если его структура соответствует XSD схеме. Вы можете связать XSD схему с переменной, колонкой или параметром xml типа. Элемент xml типа, с которым связана XSD схема, называется типизированным. Связанная с элементом схема, будет автоматически проверять его корректность при каждой модификации данных.

Вы можете создать в базе данных коллекции XSD схем, а затем использовать их при описании xml элементов. В Листинг 2.1 определяется схема SimpleXmlSchema допускающая хранение в одного xml элемента c именем root, и типом хранимых данных string.

Листинг 2.1 Создание и использование коллекции схем

create xml schema collection SimpleXmlSchema as ’ <schema xmlns="http://www.w3.org/2001/XMLSchema"> <element name="root" type="string"/> </schema>’ go declare @xmlTypedData xml (SimpleXmlSchema) set @ xmlTypedData ='<root>Hello</root>' create table T1( elem xml (SimpleXmlSchema))

Связанная с элементом схема, будет автоматически проверять его корректность при каждой модификации данных. В Листинг 2.2 продемонстрированы различные способы добавление в таблицу xml данных, удовлетворяющих схеме.

Листинг 2.2 Модификация типизированных xml элементов

insert into T1 values (‘<root>www.avalon.ru</root>’) insert into T1 values (@xmlTypedData) declare @xmlUntypedData set @xmlUntypedData=’<root>Untyped Xml</root>’ insert into T1 values (@xmlUntypedData)

Тема 3. Использование инструкций FOR XML

В Microsoft SQL Server 2000 для получения данных в формате XML использовалась инструкция SELECT… FOR XML. С помощью этой инструкции возвращаемый SELECT-ом набор данных можно было отформатировать в XML в трех различных режимах—RAW, AUTO и EXPLICIT. Однако, результат выполнения инструкции SELECT… FOR XML в SQL Server 2000 можно было обрабатывать только на клиентской стороне, нельзя было использовать в подзапросах, как параметры хранимых процедур и использование опции FOR XML EXPLICIT для создания сложных структур xml данных было непростой задачей. В 2005 версии ряд ограничений был снят и появились новые, расширенные возможности форматирования.

Самым простым режимом выборки данных в формате xml является опции FOR XML RAW. Для каждой возвращаемой строки создается один элемент с именем raw по умолчанию. Вы можете переименовать элемент raw, добавить корневой элемент к выборке, переопределить структуру.

Листинг 3.1 Использование режима FOR XML RAW

use AdwentureWorks select CustomerID, TerritoryID, CustomerType from Sales.Customer where CustomerID=1 or CustomerID=2 FOR XML RAW

Результат :

Листинг 3.2 Использование режима FOR XML RAW

use AdwentureWorks select s.SalesoRDERid, c.CustomerID, c.CustomerType, s.OrderDate, s.Status from Sales.Customer c inner join Sales.SalesOrderHeader s on c.CustomerID=s.CustomerID where c.CustomerID=1 or c.CustomerID=442 FOR XML RAW

Результат (фрагмент):

Листинг 3.3 Использование режима FOR XML RAW, ROOT

use AdwentureWorks select CustomerID, TerritoryID, CustomerType from Sales.Customer where CustomerID=1 or CustomerID=2 FOR XML RAW('Cust'), root('Customers')

Результат (фрагмент):

Листинг 3.4 Использование режима FOR XML RAW, ROOT, ELEMENTS

select CustomerID, TerritoryID, CustomerType from Sales.Customer where CustomerID=1 or CustomerID=2 FOR XML RAW('Cust'),root('Customers'), ELEMENTS

Результат (фрагмент):

Листинг 3.5 Использование режима FOR XML RAW, ROOT, ELEMENTS

Результат (фрагмент):

Режим FOR XML AUTO позволяет сформировать простейший xml фрагмент с вложенными элементами. Имена элементов и уровень вложенности генерируется на основе именования и отношений между объектами в БД.

Листинг 3.6 Использование режима FOR XML AUTO

use AdwentureWorks select CustomerID, TerritoryID, CustomerType from Sales.Customer where CustomerID=1 or CustomerID=2 FOR XML AUTO

Результат:

<Sales.Customer CustomerID="1" TerritoryID="1" CustomerType="S" /> <Sales.Customer CustomerID="2" TerritoryID="1" CustomerType="S" />

Листинг 3.7 Использование режима FOR XML AUTO

Результат:

Листинг 3.8 Использование режима FOR XML AUTO, ELEMENTS

Результат:

Режим FOR XML AUTO в сочетании с опцией TYPE позволяет избежать использования режима FOR XML EXPLICIT для формирования простых вложенных выборок. Опция TYPE прзволяет вывести результат выборки в формате xml.

Листинг 3.9 Использование режима FOR XML AUTO, TYPE для сложного форматирования

select (select s.SalesoRDERid from Sales.SalesOrderHeader s where c.CustomerID=s.CustomerID for xml auto, type), c.CustomerID, c.CustomerType, (select distinct pc.LastName from Person.Contact pc inner join Sales.SalesOrderHeader s on s.SalesPersonID=pc.ContactID where pc.ContactID=s.SalesPersonID AND c.CustomerID=s.CustomerID for xml auto, type) from Sales.Customer c where c.CustomerID=22 for xml auto, type

Результат:

В некоторых ситуациях, нестандартного форматирование xml фрагмента можно добиться с помощью режима FOR XML PATH.

Листинг 3.10 Использование режима FOR XML PATH

select CustomerID as "@CustID", TerritoryID, CustomerType from Sales.Customer where c.CustomerID=1 or c.CustomerID=442 for xml path('Cust')

Результат:

Листинг 3.11 Использование режима FOR XML PATH, TYPE

select c.CustomerID as "@custID", c.CustomerType as "@custType", (select s.SalesoRDERid as "@orderID" from Sales.SalesOrderHeader s where c.CustomerID=s.CustomerID for xml path('Order'), type), (select distinct pc.LastName as "@LastName" from Person.Contact pc inner join Sales.SalesOrderHeader s on s.SalesPersonID=pc.ContactID where pc.ContactID=s.SalesPersonID AND c.CustomerID=s.CustomerID for xml path('Person'), type) from Sales.Customer c where c.CustomerID=22 for xml path('customer'), type

Результат:

www.avalon.ru

Работа с типом данных XML SQL Server Безопасный SQL

У меня есть таблица с полем XML. Типичный XML, который он содержит;

<things> <Fruit> <imageId>39</imageId> <title>Apple</title> </Fruit> <Fruit> <imageId>55</imageId> <title>Pear</title> </Fruit> <Fruit> <imageId>76</imageId> <title>Grape</title> </Fruit> </things>

В моей таблице у меня около 50 строк, я занимаюсь только двумя полями: omId (int primary key) и omText (мои xml-данные).

То, что я пытаюсь достичь, – это способ сказать по всем XML-данным во всей таблице … дайте мне все xmlElements, где заголовок X. Или дайте мне подсчет всех элементов, которые используют imageId из 55 ,

Для получения данных я использую функции VALUE и QUERY типа данных XML.

select omID, omText.query('/things/Fruit') ,cast('<results>' + cast(omText.query('/things/Fruit') as varchar(max)) + '</results>' as xml) as Value from dbo.myTable where omText.value('(/things/Fruit/imageId)[1]', 'int') = 76

Которая работает только там, где идентификатор, который я ищу, является первым в документе. Кажется, он не ищет все xml.

По сути, набор результатов возвращается с одной строкой для каждой записи в TABLE, но я думаю, что мне нужно иметь одну строку для каждого совпадающего ELEMENT … Не совсем точно, как начать писать группу для этого.

Я начинаю чувствовать, что я делаю это сложнее, чем нужно … мысли и идеи, пожалуйста.

Solutions Collecting From Web of "Работа с типом данных XML SQL Server"

То, что я пытаюсь достичь, – это способ сказать по всем XML-данным во всей таблице … дайте мне все xmlElements, где заголовок X.

Не уверен, что я полностью понял ваш вопрос здесь – или вы ищете это? Вы бы захватили все элементы / things / Fruit «узлами» и перекрестили их с вашими «базовыми данными» в myTable – результат был бы одной строкой на элемент XML в вашем поле данных XML:

select omID, T.Fruit.query('.') from dbo.myTable cross apply omText.nodes('/things/Fruit') as T(Fruit) where T.Fruit.value('(title)[1]', 'varchar(50)') = 'X'

Или дайте мне подсчет всех предметов, которые используют изображение из 55.

select count(*) from dbo.myTable cross apply omText.nodes('/things/Fruit') as T(Fruit) where T.Fruit.value('(imageId)[1]', 'int') = 55 - select count(*) from dbo.myTable cross apply omText.nodes('/things/Fruit') as T(Fruit) where T.Fruit.value('(imageId)[1]', 'int') = 55

Это то, что вы ищете?

Марк

sql.fliplinux.com

Работа с xml в ms sql Server 2005, 2008

Лабораторная работа. XML. Семенова И.И. - 23-

Цель работы: познакомиться с понятиями XML – модель данных, XSD – схемами, познакомиться со способами создания типизированного и нетипизированного XML, а также способами преобразования реляционной модели в XML.

Для лабораторной работы использована частично теория спецкурса “Реализация баз данных Microsoft SQL Server 2008”, существенно дополненного примерами работы и дополнительными сведениями, позволяющими работать с материалом начинающим.

Подготовка к работе:

Для выполнения некоторых примеров необходимо установить учебную базу данных: для версии 2005 - AdventureWorksLT.msi (папка «пример БД для MS SQL Server 2005»), для версии 2008 - AdventureWorksLT2008_Data.mdf и AdventureWorksLT2008_Log.ldf (папка «пример БД для MS SQL Server 2008»).

Порядок установки для версии 2005:

Запустить файл AdventureWorksLT.msi на установку и следовать инструкции. В результате в домашней папке, хранящей базы данных, например: C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data, будет сформировано два файла: AdventureWorksLT_Data.mdf и AdventureWorksLT_Log.ldf.

Далее для работы запустить программу SQL Server Management Studio и присоединить базу данных, как показано в лабораторной работе №1 (на компоненте <Базы данных> (Databases) нажать правой кнопкой мыши, далее выбрать <Присоединить…> (Attach…), в появившемся окне добавить путь к файлам присоединяемой базы данных и выполнить присоединение).

Порядок установки для версии 2008:

Файлы AdventureWorksLT2008_Data.mdf и AdventureWorksLT2008_Log.ldf поместить в папку с другими файлами баз данных, например: C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

Ознакомьтесь с содержимым таблиц базы данных – она описывает покупки, заказы, покупателей и прочее.

При выполнении работы необходимо исполнять все встречающиеся примеры и в отдельный файл копировать код примера и результат выполнения. Проанализировать выполненные примеры.

Для выполнения примеров в SQL Server Management Studio нажать <Новый запрос> (New Query) в левом верхнем углу программы. Для запуска кода нажмите F5.

Использование типа данных xml Тип данных xml

Тип данных XML является встроенным типом данных SQL Server.

Он может применяться для определения столбцов таблиц, переменных, параметров или значений функций.

Тип данных XML позволяет сохранять xml–документы и фрагменты в базе данных SQL Server.

При необходимости можно назначить коллекцию xml–схем для столбца, параметра или переменной типа XML.

Схемы коллекции применяются для проверки XML–данных. В этом случае используют понятие типизированный XML.

http://msdn.microsoft.com/ru-ru/library/ms184277%28v=SQL.100%29.aspx

1. Пример создания схем для XML:

Для выполнения примера в SQL Server Management Studio нажать <Новый запрос> (New Query) в левом верхнем углу программы. Для запуска кода нажмите F5.

Use <название БД> - сделать базу данных текущей

use AdventureWorksLT

CREATE XML SCHEMA COLLECTION [dbo].[MySampleCollection]

N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="e" type="xsd:dateTime" />

</xsd:schema>'

Увидеть результат выполнения можно в разделе базы данных AdventureWorksLT, далее <Программирование> (Programmability) , далее <Типы> (Types), далее <Коллекция схем XML> (XML Schema Collection)

2. Пример: Cоздание типизированной переменной xml путем указания коллекции XML-схем:

use AdventureWorksLT

declare @x xml ([dbo].[MySampleCollection])

3. Пример: Cоздание типизированного поля xml в структуре таблицы:

use AdventureWorksLT

CREATE TABLE Table1( [Col1] [xml] (CONTENT [dbo].[MySampleCollection]) NULL)

Увидеть результат выполнения можно в разделе базы данных AdventureWorksLT, далее <Таблицы> (Tables)

studfiles.net

Работа с XML в SQL Server (часть 2) (ru-RU) - Статьи TechNet - Россия (Pусский)

Продолжение статьи про работу с XML в SQL Server Предположим, что нам нужно предоставить два различных способа описания возможных размеров продукта. Предположим, например, что одежда имеет размеры 46, 48 и 50, но также может описываться как S, M и L. Для поддержки такой возможности в SQL Server 2008 введен тип union, который содержит несколько типов list, которые могут объединяться в единый тип, описывающий возможные значения для определенного типа. В приведенном ниже примере показано, как создать XML-схему, содержащую тип productSizeType, для которого допустимыми значениями будут числовые значения (46, 48, 50) и символьные значения (S, M L).

CREATE XML SCHEMA COLLECTION CatalogSizeSchema AS

N'<?xml version="1.0" encoding="UTF_16"?>

<xs:simpleType name="productSizeType">

<xs:union>

<xs:simpleType>

<xs:list>

<xs:simpleType>

<xs:restriction base="xs:integer">

<xs:enumeration value="46"/>

<xs:enumeration value="48"/>

<xs:enumeration value="50"/>

</xs:restriction>

</xs:simpleType>

</xs:list>

</xs:simpleType>

<xs:simpleType>

<xs:list>

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:enumeration value="S"/>

<xs:enumeration value="M"/>

<xs:enumeration value="L"/>

</xs:restriction>

</xs:simpleType>

</xs:list>

</xs:simpleType>

</xs:union>

</xs:simpleType>

</xs:schema>'

При таком описании схемы элементы, базирующиеся на типе productSizeType, могут содержать значения из любого списка - в следующем примере показано, что оба элемента будут соответствовать правилам, заданным в схеме.

<ProductName>Polo Shirt</ProductName>

</Product>

<ProductName>Long_Sleeve Shirt</ProductName>

</Product>

</Catalog>

Точно также в SQL Server 2008 поддерживаются задания в схеме типов list, содержащих внутри себя типы union.

Расширения XQuery Как мы отметили выше, поддержка типа данных xml впервые появилась в SQL Server 2005. Эта поддержка, в частности, включала предоставление разработчикам ряда методов для манипуляции XML-данными, хранимыми в переменных или колонках соответствующего типа. Большинство операций над XML-данными можно выполнить, используя синтаксис XQuery, который поддерживает как навигацию по данным, так и манипуляции с этими данными. Синтаксис XQuery, поддерживаемый в SQL Server 2005, включает такие конструкции, как for, where, order by и return, которые вместе называются FLWOR_выражениями и могут использоваться для перебора ветвей XML-документа и возврата найденных значений. В SQL Server 2008 добавлена конструкция let, позволяющая присваивать значения переменным в выражениях XQuery - пример использования данной конструкции показан ниже.

declare @x xml

set @x=

'<Invoices>

<Customer>Kim Abercrombie</Customer>

<Items>

</Items>

</Invoice>

<Customer>Margaret Smith</Customer>

<Items>

</Items>

</Invoice>

</Invoices>'

SELECT @x.query(

'<Orders>

{

for $invoice in /Invoices/Invoice

let $count :=count($invoice/Items/Item)

order by $count

return

<Order>

{$invoice/Customer}

<ItemCount>{$count}</ItemCount>

</Order>

}

</Orders>')

В результате выполнения данного кода будет создан следующий XML-документ:

<Order>

<Customer>Margaret Smith</Customer>

</Order>

<Order>

<Customer>Kim Abercrombie</Customer>

</Order>

</Orders>

Обратите внимание на то, что в SQL Server 2008 не поддерживается присваивание значений элементам, создаваемым на лету.

Расширения XML DML Помимо рассмотренной выше поддержки выражений XQuery для выполнения манипуляций над XML-данными, тип данных xml поддерживает такие XML DML команды, как insert, replace value of и delete - для этого используется метод modify. Эти команды могут использоваться для манипуляции XML-данными, хранимыми в переменных или в колонках типа xml. В SQL Server 2008 также поддерживается использование переменных типа xml при выполнении команды insert для вставки XML-данных в существующую XML-структуру. Например, предположим, что переменная @productList типа xml содержит следующий документ:

<Bike>Mountain Bike</Bike>

</Products>

Мы можем использовать следующий код для вставки нового продукта в уже существующий документ:

DECLARE @newBike xml

SET @newBike = '<Bike>Racing Bike</Bike>'

@productList.modify

('insert sql:variable("@newBike") as last into (/Products)[1]')

После выполнения этого кода переменная @productList будет содержать следующий XML-документ:

<Bike>Mountain Bike</Bike>

<Bike>Racing Bike</Bike>

</Products>

social.technet.microsoft.com

добейтесь большего от SQL Server » Статьи о Microsoft Windows. Установка, администрирование, ускорение и оптимизация Microsoft Windows 7, Windows 8, Windows XP, и Windows 10

Я расскажу, как обращаться к данным, которые содержатся в XML-строках, передаваемых хранимым процедурам, и как выводить XML-данные. На рис. 1 показана высокоуровневая архитектура этих операций. Я познакомлю вас со следующими синтаксическими конструкциями T-SQL, связанными с XML:

--- sp_xml_preparedocument;--- OPENXML;--- sp_xml_removedocument;--- FOR XML EXPLICIT.

Рис. 1. Применение на практике некоторых новшеств в синтаксисе T-SQL

Простой пример: пакетная проверка пользовательского вводаВ SQL Server 2000 нет встроенного типа для хранения XML-данных. Для хранения XML-строк в переменных или полях таблиц применяют тип (n)(var)char или (n)text. Почти во всех случаях, встречающихся при разработке, такие переменные оказываются входными параметрами хранимых процедур; поэтому так будет и в моем примере. (Следует заметить, что передача кода T-SQL между уровнями всегда рискованна с точки зрения безопасности и, как правило, оказывается не лучшим вариантом с точки зрения производительности; это достаточно веские причины, чтобы никогда так не поступать.)

Рассмотрим хранимую процедуру sptxValidateLookupData, разработанную моей группой несколько лет назад. Она предназначена для проверки на допустимость данных, вводимых пользователем в рабочий процесс определенной системы. Чтобы свести к минимуму количество обменов с базой данных, разработчики системы решили накапливать весь пользовательский ввод и передавать информацию, которую требуется проверять на допустимость, в базу данных в одном XML-документе. Хранимая процедура выполняет проверку на допустимость и возвращает вызывающему процессу результаты, также помещая их в один XML-документ. Процедура sptxValidateLookupData обслуживает несколько разных рабочих процессов, поэтому в одном и том же пакете могут запрашиваться проверки "существует" или "не существует" для любого значения (datum) или области (domain). В следующем фрагменте кода показаны типичные входные XML-данные этой хранимой процедуры:

В корневом узле содержатся подузлы , описывающие, какую проверку нужно выполнить. Type задает область, для которой выполняется проверка, Name - проверяемый атрибут, а Test - тип проверки (существует ли значение Value в поле, указанном атрибутом Name, в области Type). Заметьте: этот фрагмент XML-данных описывает четыре операции проверки на допустимость для трех областей, но версия хранимой процедуры, которая используется в настоящее время, поддерживает 17 областей и любое число проверок. Таким образом, у нас есть компонент проверки на допустимость, обеспечивающий многократное использование кода, отличную расширяемость и высокую производительность, удобный в сопровождении, а главное - простой до неприличия!

Результаты также возвращаются в XML-формате. Если все проверки для данного потока возвратили TRUE (т. е. если истинны утверждения, задаваемые в узлах Test), возвращается пустой тэг . Однако, если какие-либо проверки потерпели неудачу, возвращается список ошибок:

Работа с XML-даннымиВ SQL Server 2000 XML-данные передаются процессу T-SQL как простые строки. Чтобы с ними можно было работать как с реляционными или иерархическими данными, необходимо дать SQL Server "понять", что это XML-данные, - "подготовить" их. Для этого вызывается системная хранимая процедура sp_xml_preparedocument. Давайте посмотрим начало хранимой процедуры sptxValidateLookupData (листинг 1). Системная хранимая процедура sp_xml_preparedocument считывает XML-текст, передаваемый как входной параметр (параметр @XMLString в вызове в листинге 1), затем передает текст анализатору MSXML и формирует проанализированный документ, готовый к обработке функцией OPENXML, возвращающей набор записей.

Этот документ является иерархическим представлением различных узлов XML-документа (элементов, атрибутов, текста, комментариев и т.д.). Оно хранится в кэше сервера, максимальный размер которого равен одной восьмой от общего объема памяти сервера. Поэтому необходимо аккуратно работать с этим внутренним представлением в интенсивно используемых системах или в средах с ограниченным объемом памяти.

Листинг 1. Хранимая процедура sptxValidateLookupData

create procedure sptxValidateLookupData@XMLString ntextas

set nocount on

declare @idoc int, @Name nvarchar(30), @Value nvarchar(300), @Test nvarchar(30), @Result int, @Type nvarchar(30), @TestResult int

-- Готовим входные XML-данные к выборке с помощью OPENXMLexec sp_xml_preparedocument @idoc OUTPUT, @XMLString

Процедура sp_xml_preparedocument возвращает описатель (значение @idoc в вызове в листинге 1), через который можно обращаться к созданному внутреннему представлению XML-документа. Это значение используется в качестве параметра функции OPENXML, возвращающей набор записей для оператора SELECT. OPENXML - "мост" к подготовленному XML-документу, и ее можно указывать в операторе SELECT аналогично таблице или представлению (листинг 2).

Я создал и заполнил табличную переменную, чтобы не выполнять многократные вызовы OPENXML, которые были бы менее эффективны, чем многократное обращение к табличной переменной. Лучше копировать содержимое XML-документов в табличные переменные, чтобы избавиться от многократного неэффективного доступа к XML-данным (хотя, если вы собираетесь обратиться к XML-данным только один раз, просто выполните запрос с OPENXML без предварительного создания табличной переменной). Второй оператор на рис. 3 копирует входные XML-данные в табличную переменную @tempValidateLookupData.

Листинг 2. Создание табличной переменной

-- Создаем табличную переменную для хранения-- данных о проверкахdeclare @tempValidateLookupData table ( [Type] nvarchar(30), [Name] nvarchar(30), [Value] nvarchar(300), [Test] nvarchar(30), [TestResult] int)

-- Заполняем табличную переменную данными о проверках,-- которые требуется выполнитьinsert @tempValidateLookupDataselect [Type], [Name], [Value], [Test], NULL from OPENXML (@idoc, '/ValidateData/Validate') with ([Type] nvarchar(30), [Name] nvarchar(30), [Value] nvarchar(300), [Test] nvarchar(30))

Знакомимся с OPENXMLТеперь повнимательнее рассмотрим синтаксис OPENXML:

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]])[WITH (SchemaDeclaration | TableName)]

Параметр idoc - описатель документа, созданный для внутреннего представления XML-документа. Другими словами, это значение, ранее возвращенное процедурой sp_xml_preparedocument. Заметьте: в одной хранимой процедуре можно манипулировать несколькими XML-строками. В таком случае необходимо вызвать sp_xml_preparedocument для каждой XML-строки и объявить по отдельной переменной для хранения каждого возвращенного описателя.

Параметр rowpattern идентифицирует узлы XML-документа, связанного с описателем idoc, которые должны обрабатываться как записи. Он указывает XML-анализатору, где в XML-документе находятся интересующие вас данные.

Параметр flags задает, какое сопоставление используется при запросе в первую очередь - ориентированное на атрибуты (attribute-centric) или на элементы (element-centric). Если этот параметр опущен, SQL Server 2000 по умолчанию применяет сопоставление, ориентированное на атрибуты, что вполне подходит для нашего случая. Подробнее об этом параметре см. описание функции OPENXML в SQL Server Books Online.

Раздел WITH функции OPENXML указывает SQL Server 2000, какие типы данных SQL сопоставляются содержимому XML-документа. Вы можете либо явно задать поля, либо сослаться на таблицу базы данных с подходящей структурой. Каждое поле XML-документа, используемое в операторе SELECT, должно присутствовать в разделе WITH (описываться явно или в таблице). В рассматриваемом примере:

имена полей в операторе SELECT и в разделе WITH должны соответствовать именам атрибутов во входном XML-документе. Также заметьте, что, поскольку XML-данные рассматриваются как набор записей, программа проверки на допустимость будет правильно работать при любом количестве значений (хоть при одном, хоть при десяти): вызывающий процесс просто передает столько узлов XML-документа, сколько нужно проверить на допустимость.

Освобождение памятиВнутреннее представление XML-документа остается в памяти, пока соединение процесса с сервером не будет закрыто или сброшено или пока память не освободят явно. Следует вызывать системную процедуру sp_xml_removedocument как можно раньше, поскольку лучше поскорее освободить эту память, чтобы на сервере было доступно больше ресурсов (для освобождения памяти требуется указать описатель):

-- Освобождаем память сервера, которая используется-- образом входных XML-данных, созданным OPENXMLexec sp_xml_removedocument @idoc

Это особенно важно в средах, где используется пул соединений. В таких средах соединения могут существовать в течение нескольких дней, и многократное создание образов XML-документов в памяти без последующего освобождения может вызвать снижение производительности, поскольку количество свободной памяти сократится. Пул соединений сбрасывает соединение, если оно повторно используется кем-то другим, при этом освобождаются и описатели XML-данных. Но лучше явно освобождать описатели, а не полагаться, на то, что при сбросе соединений это сделают за вас.

После разбора XML-документа процедура sptxValidateLookupData выполняет большой оператор IF, чтобы выбрать требуемую проверку и занести в переменную @TestResult значение, соответствующее результатам проверки. Затем результаты проверки используются при обновлении табличной переменной (листинг 3).

Листинг 3. Выбор требуемой проверки

-- Заносим результаты проверок в табличную переменную,-- перебирая записи, у которых поле TestResult содержит NULLwhile exists (select TestResult from @tempValidateLookupData where TestResult is null) begin

-- Извлекаем данные из записи, проверяемой на допустимость Select top 1 @Type=[Type], @Name=[Name], @Value=Value, @Test=Test from @tempValidateLookupData where TestResult is null

-- И выполняем соответствующую проверку...

-- Проверка для области Countries (взята в качестве примера) if @Type = 'Countries' begin if exists (select CountryCode from dbo.Country where CountryCode = convert(nvarchar(4), @Value)) select @TestResult = CASE when @Test = 'Exists' then 1 else 0 end else select @TestResult = CASE when @Test = 'Not Exists' then 1 else 0 end end

-- (16 других проверок на допустимость опущены для краткости)

-- Обновляем соответствующую запись: заносим в нее результат-- проверки update @tempValidateLookupData set TestResult = @TestResult where Name = @Name and Value = @Value and Test = @Testend

SQL-синтаксис для возврата XML-данныхИтак, проверки на допустимость выполнены, теперь нужно возвратить результаты вызывающему процессу. SQL Server 2000 поддерживает несколько механизмов вывода XML-данных с помощью директивы FOR XML оператора SELECT. Прежде чем продолжить рассказ о sptxValidateLookupData, я кратко рассмотрю еще кое-какие новшества в синтаксисе языка SQL.

В SQL Server 2000 три типа разделов FOR XML. FOR XML RAW и FOR XML AUTO позволяют сформировать простейший XML-вывод с минимумом усилий и соответственно с отсутствием контроля над форматом вывода. Большинство уважающих себя программистов для SQL Server 2000 используют FOR XML EXPLICIT. При применении режима EXPLICIT программист полностью контролирует вид XML-документа, возвращаемого запросом, и должен обеспечить синтаксическую корректность и допустимость XML-документа.

Как выглядит содержимое раздела EXPLICIT?Существует ряд жестких синтаксических требований к формированию запросов, использующих режим EXPLICIT. Каждый запрос с режимом EXPLICIT должен содержать два поля метаданных. У первого поля, указываемого в операторе SELECT, должно быть имя Tag и тип int. Это номер тэга текущего элемента, т. е. фактически номер типа поддерева. У второго поля должно быть имя Parent и тоже тип int. Оно содержит номер тэга элемента, который является родителем текущего элемента. Эти поля описывают иерархию XML-дерева. Если поле Parent записи имеет значение 0 или NULL, его данные располагаются на вершине XML-иерархии. Кроме этого единственного исключения, все значения поля Parent должны соответствовать ранее объявленным значениям тэгов. Заметьте: набор результатов должен содержать ровно одну запись, у которой поле Parent содержит 0 или NULL, причем она должна быть первой в наборе результатов (если имеется более одной записи, у которой номер тэга родителя равен 0 или NULL, генерируется XML-фрагмент).

Имена остальных полей запроса должны соответствовать специальному формату, который определяет, как имена элементов связаны с номерами тэгов, и задает имена атрибутов для генерируемых XML-данных. Имя поля имеет формат:

[ElementName!TagNumber!AttributeName!Directive]

где ElementName - имя элемента (если в качестве ElementName указано "Countries", результатом будет ), а TagNumber - номер тэга элемента. TagNumber вместе с полями метаданных Tag и Parent описывает иерархию XML-дерева. Каждый TagNumber соответствует одному ElementName, а AttributeName является именем XML-атрибута (если оно задано).

Рассмотрение использования Directive и работы в нескольких ситуациях, в которых AttributeName может быть NULL, выходит за рамки данной статьи. Раздел "Using Explicit Mode" в SQL Server 2000 Books Online - превосходный источник дополнительной информации.

Кроме того, для формирования корректных XML-данных набор результатов должен быть упорядочен так, чтобы каждый родитель шел непосредственно перед своими потомками.

Вернемся к sptxValidateLookupData. Как вы помните, в описании процедуры говорилось о двух форматах возвращаемого набора результатов. Если все проверки данного пакета возвратили TRUE, я возвращаю пустой тэг . Но если какая-то проверка потерпела неудачу, я возвращаю XML-документ со списком ошибок.

В листинге 4 приведен код процедуры sptxValidateLookupData, который генерирует наборы результатов с помощью FOR XML EXPLICIT. Синтаксис режима EXPLICIT довольно многословен, поэтому рассмотрим его по частям. Первая ветвь оператора IF обрабатывает простую ситуацию, когда все мои проверки возвратили TRUE:

-- Если все проверки на допустимость пройдены,-- возвращаем пустой набор XML-данных...if not exists (select [TestResult] from @tempValidateLookupData where TestResult = 0) select 1 as TAG, 0 as parent, NULL as [Results!1!] for xml explicit

Листинг 4. Применение FOR XML EXPLICIT

-- ...иначе возвращаем XML-данные о проверках,-- потерпевших неудачуelse select 1 as TAG, 0 as parent, NULL as [Errors!1!], NULL as [Error!2!], NULL as [Error!2!ErrorMessage], NULL as [Error!2!FieldName]

union all

select 2 as TAG, 1 as parent, NULL, NULL, ltrim(rtrim(value)) + ' does not exist in ' + type, [name]

from @tempValidateLookupData where [TestResult] = 0 and test = 'Exists'

union all

select 2 as TAG, 1 as parent, NULL, NULL, ltrim(rtrim(value)) + ' already exists in ' + type, [name]

from @tempValidateLookupData where [TestResult] = 0 and test = 'Not Exists'

for xml explicit

Поскольку AttributeName не задано, этот оператор создаст единственный XML-элемент Results без атрибутов и потомков:

Ветвь ELSE гораздо интереснее: в ней я формирую XML-данные о проверках, потерпевших неудачу, объединяя операторы SELECT с помощью UNION. Как показано в листинге 5, каждый запрос формирует узлы выходных XML-данных определенного типа (который описывается в комментариях после каждого запроса).

Листинг 5. Формирование узлов

-- (приведенный выше запрос формирует элемент <Errors>)

union all

select 2 as TAG, 1 as parent, NULL, NULL, ltrim(rtrim(value)) + ' does not exist in ' + type, [name]

from @tempValidateLookupData where [TestResult] = 0 and test = 'Exists'

-- (приведенный выше запрос формирует узлы вида-- <Error ErrorMessage="BO1 does not exist in Countries"-- FieldName="CountryCode"/>)

union all

select 2 as TAG, 1 as parent, NULL, NULL, ltrim(rtrim(value)) + ' exists in ' + type, [name]

from @tempValidateLookupData where [TestResult] = 0 and test = 'Not Exists'

-- (приведенный выше запрос формирует узлы вида-- <Error ErrorMessage="JA exists in Countries"-- FieldName="CountryCode"/>)

for xml explicit

Заметьте: запросы, объединенные UNION, упорядочены так, чтобы каждый потомок шел сразу за своим родителем, хотя узлы могут быть потомками нескольких подзапросов (у запросов "exists" и "not exists" поля tag и parent имеют одинаковые значения). Когда запросы, объединенные с помощью UNION, обрабатываются в режиме FOR XML EXPLICIT, сериализатор FOR XML вычисляет имена полей

[ElementName!TagNumber!AttributeName!Directive]

и значения полей метаданных Tag и Parent, а затем выводит XML-иерархию, заданную программистом:

Миссия выполненаИмея в распоряжении описанные выше средства, вы можете запрашивать и генерировать XML-данные в среде SQL Server 2000. Но какими бы мощными ни были эти средства, SQL Server 2005 предоставит еще больше программных функций всем специалистам, обрабатывающим XML-данные с помощью T-SQL, и позволит добиться еще большей производительности.

XML-программирование в SQL Server 2005При разработке SQL Server 2005 Microsoft значительно усовершенствовала поддержку XML. Последние два года я создавал новую систему на основе внутренних версий этого продукта (рай для SQL-разработчика!) и рад сообщить, что программистов, использующих XML, ждет масса хороших новостей.

Весь синтаксис поддержки XML, введенный в SQL Server 2000, не изменился, но в SQL Server 2005 в него внесена уйма дополнений. Я рассмотрю некоторые из них и покажу, как изменится хранимая процедура sptxValidateLookupData, рассмотренная в статье. Я также расскажу о следующих новшествах SQL Server 2005: о типах данных XML, FOR XML PATH, TYPE, синтаксисе nodes().

Все методики работы с XML, применяемые в SQL Server 2000 (передача XML-строк хранимым процедурам в параметрах типа ntext, манипулирование ими с помощью sp_xml_preparedocument, sp_xml_removedocument, FOR XML EXPLICIT и OPENXML), доступны и в SQL Server 2005, но в новой версии появились средства, позволяющие использовать другие подходы. Как только вы поработаете с новым синтаксисом, вам уже не захочется возвращаться к старому.

В SQL Server 2005 внесены революционные изменения в сам механизм хранения данных, в частности введены три новых типа данных: nvarchar(max), varbinary(max) и xml. В T-SQL возникали сложности с обработкой значений типа ntext, поэтому, если вы собираетесь по-прежнему использовать те же конструкции работы с XML, что и в SQL Server 2000, имеет смысл отказаться от ntext и перейти на nvarchar(max) и varbinary(max). Однако наиболее интересен тип данных XML, также добавленный в ядро сервера. Он предоставляет программистам ряд совершенно новых возможностей.

Переработанный примерКак вы помните, хранимая процедура sptxValidateLookupData, рассмотренная в статье, поддерживает единый процесс пакетной проверки пользовательского ввода в Web-страницы, позволяющий выполнить несколько разных проверок на допустимость для значений, получаемых из гетерогенных источников. Директивы, описывающие проверки, передаются хранимой процедуре в одном XML-документе. Хранимая процедура выполняет проверки и возвращает результаты вызывающему процессу, тоже в одном XML-документе.

Новая версия процедурыКак и в процедуре для SQL Server 2000, я помещаю содержимое входного XML-документа в табличную переменную. Но я больше не вызываю sp_xml_preparedocument и не применяю OPENXML. Как показано в листинге 6, я изменил тип данных параметра на xml (с ntext) и воспользовался новым синтаксисом nodes().

Листинг 6. Использование типа данных XML

create procedure sptxValidateLookupData@XMLString xmlas

set nocount on

declare @idoc int, @Name nvarchar(30), @Value nvarchar(300), @Test nvarchar(30), @Result int, @Type nvarchar(30), @TestResult int

-- Заполняем табличную переменную данными о проверках,-- которые требуется выполнитьinsert @tempValidateLookupDataselect ref.value ('@Type', '[nvarchar](30)'), ref.value ('@Name', '[nvarchar](30)'), ref.value ('@Value', '[nvarchar](300)'), ref.value ('@Test', '[nvarchar](30)'), NULL from @XMLString.nodes('/ValidateData/Validate') as node(ref)

Давайте повнимательнее рассмотрим метод nodes. Он позволяет получить ссылку для каждой записи, которая соответствует элементу Validate, находящемуся внутри элемента ValidateData переменной @XMLString. Эта переменная описывается инструкцией AS NODE(ref) как набор записей, представляющий узлы (node rowset). В каждом определении поля в операторе SELECT вызывается метод value, извлекающий значение заданного атрибута. Он выполняется для каждой записи, поэтому для каждого элемента Validate с параметрами проверки генерируется по одной записи.

В каждом вызове метода value указывается имя атрибута элемента, извлекаемого из XML-узла. Перед именем атрибута ставится символ @, и оно заключается в одинарные кавычки, затем (также в кавычках) указывается тип данных SQL, сопоставляемый атрибуту.

Логика выполнения проверок на допустимость в новой версии sptxValidateLookupData осталась прежней.

Возврат результатовКак вы помните, в исходной спецификации процедуры описывались два формата возвращаемого набора записей. Если все проверки данного пакета возвратили TRUE, набор должен содержать пустой тэг . Однако, если какие-то проверки потерпели неудачу, нужно вернуть список XML-данных об ошибках.

В листинге 7 приведен новый код процедуры sptxValidateLookupData, генерирующий наборы результатов с помощью FOR XML PATH, TYPE. Обратите внимание, насколько этот синтаксис компактнее старого синтаксиса FOR XML EXPLICIT.

Листинг 7. Применение FOR XML PATH

-- ...иначе возвращаем XML-данные о проверках,-- потерпевших неудачуelse select null, ( select ltrim(rtrim(value)) + ' does not exist in ' + type AS '@ErrorMessage', [name] AS '@FieldName'

from @tempValidateLookupData where [TestResult] = 0 and test = 'Exists'

for xml path ('Error'), type ), ( select ltrim(rtrim(value)) + ' already exists in ' + type AS '@ErrorMessage', [name] AS '@FieldName'

from @tempValidateLookupData where [TestResult] = 0 and test = 'Not Exists'

for xml path ('Error'), type ) for xml path ('Errors'), type

Как и прежде, первая ветвь оператора IF обрабатывает простой случай, когда все проверки возвратили TRUE:

Ветвь ELSE, как и раньше, формирует XML-данные для проверок, потерпевших неудачу, но на сей раз с помощью нескольких вложенных операторов SELECT FOR XML PATH, TYPE. Явно заданные имена полей указывают SQL Server, что их нужно использовать в качестве имен XML-атрибутов (перед ними должен идти символ @, их надо заключить в одинарные кавычки). Директивы FOR XML PATH ('Error') указывают серверу, что требуется обернуть XML-данные, создаваемые внутренними операторами SELECT, элементом Error, а директива FOR XML PATH ('Errors') - что внешний SELECT формирует корневой элемент 'Errors'. Таким образом, этот SQL-код генерирует тот же набор результатов, что и прежде, но с помощью гораздо более лаконичного запроса, чем запрос с FOR XML EXPLICIT.

Может, на первый взгляд это и не очевидно, но отказ от запросов SELECT, объединенных операторами UNION, выполняемых при использовании FOR XML EXPLICIT, делает код гетерогенных запросов гораздо компактнее и удобнее в сопровождении. Например, недавно моя группа переписала одну пользовательскую функцию SQL Server 2000 длиной в 5000 строк (большую их часть составлял 43-уровневый запрос с FOR XML EXPLICIT). Новый синтаксис позволил уложиться в 497 строк.

Если вы хотите по-прежнему придерживаться модели с UNION (что сомнительно), то можете написать второй запрос SELECT так, как показано в листинге 8. Этот подход не сработал бы, если бы ваши внутренние XML-узлы содержали разные количества атрибутов. Одно из основных преимуществ нового вложенного синтаксиса в том, что в отличие от операторов SELECT с UNION количество полей в подзапросах не обязательно должно быть одинаковым. Тем не менее, пример в листинге 8 демонстрирует еще одну новую конструкцию - FOR XML ROOT. Она позволяет объявить корневой элемент возвращаемых XML-данных, не создавая явный запрос SELECT. Дополнительные сведения по этому вопросу см. в статье "XML Options in Microsoft SQL Server 2005" (EN).

Листинг 8. Применение FOX XML ROOT

select ltrim(rtrim(value)) + ' does not exist in ' + type AS '@ErrorMessage', [name] AS '@FieldName'

from @tempValidateLookupData where [TestResult] = 0 and test = 'Exists'

union all

select ltrim(rtrim(value)) + ' already exists in ' + type AS '@ErrorMessage', [name] AS '@FieldName'

from @tempValidateLookupData where [TestResult] = 0 and test = 'Not Exists'

for xml path ('Error'), root ('Errors'), type

С помощью этих новых средств SQL Server 2005 вы можете запрашивать и генерировать XML-данные еще эффективнее, чем раньше. Я рассмотрел лишь небольшую часть возможностей нового синтаксиса. SQL Server 2005 позволяет делать с XML-данными почти все, что угодно. Если вас интересуют ресурсы с самой свежей информацией о SQL Server 2005, посетите сайт "Introducing SQL Server 2005" (EN).

Оцените статью: Голосов 1