В SQL Server 2000 имеется ряд функций поддержки XML, позволяющих преобразовывать реляционные наборы данных в иерархические XML-документы, читать XML-документы и выполнять массовую загрузку данных из XML-документов. Например, можно передать XML-документ хранимой процедуре, объединить XML-данные с какими-либо таблицами и возвратить набор записей или даже изменить данные в базе. Введение функции OPENXML и раздела FOR XML отражает тот факт, что роль XML в современных системах уровня предприятия постоянно растет. Благодаря этим средствам SQL Server не только поддерживает XML, но и может более эффективно выполнять массовую загрузку данных.
Сегодня я расскажу, как возвращать XML-данные при запросах к SQL Server, включая в операторы T-SQL раздел FOR XML. Я приведу примеры получения XML-данных и информации о схеме и покажу, как преоб
разовать XML-данные в более приятный формат. Затем я рассмотрю функцию OPENXML, объединение XML-документов с таблицами баз данных и получение XML-данных из DataSet с помощью методов WriteXml и GetXml. SQL-код примеров и проект ASP.NET, выполняющий некоторые примеры и выводящий результаты в текстовые файлы, можно скачать с сайта MSDN Magazine. Кроме того, в пример проекта включен код вставки и обновления записей базы данных информацией, содержащейся в XML-данных.
Возвращение XML
Раздел FOR XML оператора SELECT указывает SQL Server, что данные нужно вернуть в XML-формате, а не как стандартный набор записей. Вы можете выбрать один из трех режимов: RAW, AUTO или EXPLICIT. Каждый из них задает определенный способ преобразования данных в XML-формат. (В табл. 1 дано краткое описание каждого режима.)
Например, если вы зададите режим FOR XML RAW при запросе к таблице Employees базы данных North-wind, запрос возвратит данные каждой записи о сотруднике в элементе <row>. Каждое поле, включенное в оператор SELECT, будет представлено атрибутом элемента <row>. Следующий запрос с разделом FOR XML RAW выберет две записи о сотрудниках и возвратит их в RAW-формате:
SELECT EmployeeID, FirstName, LastNameFROM EmployeesWHERE LastName LIKE 'D%'FOR XML RAW<row EmployeeID="1" FirstName="Nancy" LastName="Davolio"/><row EmployeeID="9" FirstName="Anne" LastName="Dodsworth"/>
Можно изменить этот оператор SELECT, задав другой режим - FOR XML AUTO. Тогда будут возвращаться элементы Employees, т. е. их именем будет имя исходной
исходной таблицы. Поля, как и в предыдущем случае, будут атрибутами этих элементов:
SELECT EmployeeID, FirstName, LastNameFROM EmployeesWHERE LastName LIKE 'D%'FOR XML AUTO<Employees EmployeeID="1" FirstName="Nancy" LastName="Davolio"/><Employees EmployeeID="9" FirstName="Anne" LastName="Dodsworth"/>
Преобразование и иерархии
Данные, возвращаемые в предыдущих примерах, отличались не так уж сильно, однако, когда используются запросы с объединением таблиц, различия между AUTO и RAW оказываются более значительными. Запрос с разделом FOR XML RAW возвращает только элементы <row> независимо от того, из скольких таблиц берутся данные. Таким образом, в режиме RAW не используется иерархическая структура, присущая XML-документам. Рассмотрим следующий SQL-оператор:
SELECT Customers.CustomerID, CompanyName, OrderID, CONVERT(VARCHAR(10), OrderDate, 101) AS OrderDateFROM CustomersINNER JOIN Orders ON Customers.CustomerID = Orders.CustomerIDORDER BY Customers.CustomerID
В этом примере данные выбираются из таблиц, связанных отношением один-ко-многим. Выполнив этот SQL-оператор, я получу набор записей с данными о покупателях и их заказах. Если я добавлю раздел FOR XML RAW и снова выполню запрос, возвращенные XML-данные будут содержать по одному элементу <row> для каждой записи. Например, XML-данные на рис. 1 представляют записи, возвращаемые запросом с разделом FOR XML RAW для покупателя с идентификатором (CustomerID) ALFKI.
Заметьте: данные не упорядочены в соответствии с иерархией родитель-потомок. Если нужно, чтобы данные имели вид набора элементов <Customers>, содержащих элементы <Orders> с данными о соответствующих заказах, выберите другой режим - FOR XML AUTO (вложение элементов для родителей и потомков зависит от того, как сгруппированы родительские и дочерние записи). Например, XML-данные, возвращаемые для покупателя с идентификатором ALFKI, будут выглядеть так:
<Customers CustomerID="ALFKI" CompanyName= "Alfreds Futterkiste"> <Orders OrderID="10643" OrderDate="08/25/1997" /> <Orders OrderID="10692" OrderDate="10/03/1997" /> <Orders OrderID="10702" OrderDate="10/13/1997" /> <Orders OrderID="10835" OrderDate="01/15/1998" /> <Orders OrderID="10952" OrderDate="03/16/1998" /> <Orders OrderID="11011" OrderDate="04/09/1998" /></Customers>
Эти XML-данные гораздо удобнее для чтения, поскольку в них именами элементов являются имена таблиц. Кроме того, эти данные меньше по объему, поскольку
в отличие от режима FOR XML RAW для каждого элемента Orders не повторяются атрибуты CustomerID и CompanyName.
Если вы предпочитаете, чтобы значения полей представлялись элементами, а не атрибутами, вам повезло. Когда в разделе FOR XML указан параметр ELEMENTS, все значения полей становятся элементами XML-данных. Лучше один раз увидеть, чем сто раз услышать, поэтому я показал на рис. 2, как будет выглядеть вывод предыдущего запроса при задании параметра ELEMENTS. (Для краткости я привел XML-данные только для покупателя с идентификатором ALFKI. На самом деле XML-данные, генерируемые при этом запросе, будут содержать XML-элементы для всех покупателей и всех заказов.) Запрос с параметром ELEMENTS имеет следующий вид:
SELECT Customers.CustomerID, CompanyName, OrderID, CONVERT(VARCHAR(10), OrderDate, 101) AS OrderDateFROM CustomersINNER JOIN Orders ON Customers.CustomerID = Orders.CustomerIDORDER BY Customers.CustomerIDFOR XML AUTO, ELEMENTS
Возврат XML-данных при работес ADO.NET
Я включил в примеры кода к статье ASP.NET-проект, позволяющий выполнять некоторые из приведенных мной запросов (рис. 3). В этом проекте используется метод GetFORXML, который выполняет любой SQL-оператор с разделом FOR XML и выводит в файл полученные XML-данные. Для простоты эти XML-данные обернуты элементом <root>.
Код метода GetFORXML (рис. 4) демонстрирует, как с помощью стандартного объекта SqlCommand выполнить SQL-оператор с разделом FOR XML. Я использовал метод ExecuteXmlReader, чтобы вернуть XML-данные через объект XmlReader. Метод ReadXml объекта DataSet принимает XmlReader в первом аргументе, а второй аргумент указывает, что XML-данные являются фрагментом. Поскольку это фрагмент, загружаются только все узлы Customer (если имеется соответствующая схема). Поэтому перед загрузкой XML-данных я загружаю схему (подразумеваемую в данном случае) методом ReadXmlSchema. Наконец, я присваиваю свойству DataSetName объекта DataSet значение «root», в результате чего прочитанные XML-данные заключаются в родительский тэг <root/>.
ASP.NET-страница на рис. 3 позволяет выполнять SQL-операторы и получать XML-данные для примеров, которые я приводил выше, а также получать данные о сотрудниках, используя режим FOR XML RAW, BINARY BASE64. Кроме того, с ее помощью можно выполнить операторы, возвращающие XML-данные о покупателях и заказах, используя режимы:
• FOR XML RAW;
• FOR XML AUTO;
• FOR XML AUTO, ELEMENTS;
• FOR XML AUTO, ELEMENTS, XMLDATA.
Ограничения FOR XML
Ни FOR XML AUTO, ни FOR XML RAW не поддерживают полностью возврат двоичных данных. Так, если вы попытаетесь выбрать двоичное поле (вроде Employees.Photo) и укажете режим FOR XML RAW, то получите неприятное сообщение об ошибке. Один из способов обойти это ограничение - возвращать URL двоичного поля. Для этого сначала нужно задать в IIS виртуальный каталог для SQL Server. Другой вариант, при котором не требуется конфигурировать IIS, - указать параметр BINARY BASE64 в разделе FOR XML, например:
SELECT EmployeeID, FirstName, LastName, PhotoFROM EmployeesWHERE LastName LIKE 'D%'FOR XML RAW, BINARY BASE64
Этот параметр указывает SQL Server, что нужно преобразовать двоичные данные в формат BASE64. Хорошая новость в том, что теперь ваш SQL-оператор будет работать корректно. Плохая - что результаты запроса с параметром BINARY BASE64 неудобны для чтения. Ниже приведен пример XML-данных, возвращаемых при использовании BINARY BASE64. Я урезал содержимое атрибута Photo, поскольку его длина составляет более 14000 символов!
<row EmployeeID="1" FirstName="Nancy" LastName="Davolio" Photo="FRw...f4="/>
Еще одно ограничение режима FOR XML AUTO - не поддерживаются раздел GROUP BY и агрегатные функции (aggregate functions). Однако это ограничение можно обойти: выбрать записи в переменную TABLE, а затем выбрать данные из этой промежуточной таблицы, указав режим FOR XML AUTO. Следующий SQL-код показывает, как это делается:
DECLARE @TempTable TABLE (OrderID INT, Total MONEY)INSERT INTO @TempTableSELECT OrderID, SUM(UnitPrice * Quantity) AS TotalFROM [Order Details]GROUP BY OrderIDSELECT OrderID, Total FROM @TempTable AS OrderDetails FOR XML AUTO
При использовании FOR XML необходимо корректно работать с вычисляемыми полями. Режим FOR XML поддерживает вычисляемые поля, но их нужно правильно именовать. Проблемы могут возникнуть, когда оператор с разделом FOR XML пытается создать атрибут для каждого поля. Именем каждого атрибута становится имя поля, с которым он связывается. Поэтому, если для вычисляемого поля не задан синоним (alias), SQL Server не сможет выполнить запрос. Простое решение - указывать синонимы для вычисляемых полей.
Ниже показан пример такого SQL-оператора и возвращаемых им XML-данных:
SELECT TOP 1 LastName + ', ' + FirstName AS FullNameFROM EmployeesFOR XML AUTO<Employees FullName="Davolio, Nancy"/>
Последнее, о чем стоит сказать при рассмотрении режима FOR XML, - специальные XML-символы преобразуются XML-кодированием. В HTML-формате для представления специальных символов применяются URL. В синтаксически корректных XML-документах специальные символы также кодируются. Например, если данные содержат символ <, он будет преобразован в «<».
OPENXML
Пока что мы говорили о получении XML-данных от SQL Server. После этой операции было бы логично изменить XML-данные и использовать их при обновлении информации в базе данных. Позвольте представить функцию OPENXML. Функция T-SQL OPENXML формирует реляционный набор записей по XML-потоку. Этот набор записей можно использовать как таблицу, значит, его можно указывать в других SQL-операторах, например при объединении с другими таблицами и даже при вставке или обновлении данных. Это очень удобно, поскольку приложение может передать XML-поток, содержащий новые или обновленные записи, используемые при изменении информации в базе данных. Чтобы задать, откуда берутся считываемые данные, в функции OPENXML используются XPath-выражения.
Рассмотрим XML-документ, содержащий данные о покупателе, и вставим их в таблицу Customers базы данных Northwind. В этом XML-документе данные о покупателе могут представляться элементами или атрибутами, например:
<root> <customer> <custid>77777</custid> <custname>fake customer</custname> <city>Somewhere</city> <country>USA</country> </customer></root>
Функция OPENXML позволяет выбрать из XML-документа данные, соответствующие XPath-выражению, значит, формат XML-данных может быть самым разным.
Я создал хранимую процедуру (рис. 5), которая принимает этот XML-документ, готовит его к преобразованию в реляционный набор записей, читает данные о покупателе, вставляет их в таблицу Customers и удаляет XML-документ из памяти. Давайте посмотрим, как все это работает. Прежде всего замечу, что XML-данные можно передавать в переменной любого строкового типа [такого как VARCHAR(8000) или даже большой объект - TEXT или NTEXT]. Я использовал NTEXT, чтобы не ограничивать размер XML-данных 8000 символами, как требует тип VARCHAR. Максимальная длина NTEXT составляет 230 - 1 (1 073 741 823) символа. (Конечно, в SQL Server 2005 вместо этого типа можно использовать тип данных XML.)
Сначала я передаю XML-данные системной хранимой процедуре sp_xml_preparedocument. Она принимает XML-данные и преобразует их во внутреннее DOM-представление, по которому функция OPENXML формирует набор записей. Эта процедура также создает ссылку на DOM-представление, хранящееся в памяти (переменную @iDoc на рис. 5). В разделе FROM оператора SELECT можно обращаться к этим XML-данным как к набору записей, передавая переменную @iDoc функции OPENXML.
Завершив использование XML-данных в процедуре, следует удалить DOM-представление из памяти вызовом системной хранимой процедуры sp_xml_removedocument.
Центральное место в коде на рис. 5 занимает функция OPENXML, работающая с представлением XML-документа в памяти. Метод OPENXML принимает в первом аргументе ссылку на XML-документ. Второй аргумент служит для того, чтобы указать функции OPENXML, какие узлы XML DOM сопоставля
ются записям. В этом примере я идентифицирую узлы Customers, чтобы получить данные о покупателях, поэтому я указал во втором аргументе «/root/customer». Третий аргумент функции OPENXML задает тип сопоставления: значение 1 указывает, что записи сопо-ставляются атрибутам, а значение 2 - что записи сопо-ставляются элементам.
Раздел WITH позволяет задавать поля, в которые считывается информация из XML-документа, и их типы. Кроме того, в этом разделе можно сопоставить поле элементу или атрибуту XML-данных, указав XPath-выражение или даже синоним XML-поля, используемого в запросе. При вызове функции OPENXML на рис. 5 из XML-данных о каждом покупателе считываются четыре поля, помещаемые в набор записей. Из этого набора выбираются записи, вставляемые в таблицу Customers.
Вставка с помощью OPENXML
Теперь рассмотрим следующий фрагмент XML-данных и вставим содержащиеся в нем данные о заказе и позициях заказа:
<Customer CustomerID='ALFKI' <Order OrderDate='1/1/1972' Freight='3'> <Detail ProductID='1' Price='4' Quantity='10' Discount='0'/> <Detail ProductID='2' Price='5' Quantity='2' Discount='0'/> </Order></Customer>
Я передаю этот XML-фрагмент хранимой процедуре, показанной на рис. 6. В ней я сначала выполняю подготовку данных с помощью системной хранимой процедуры sp_xml_preparedocument. Затем, поскольку мне нужно вставить запись с данными о заказе и ее дочерние записи, я начинаю транзакцию, обертывающую операторы INSERT. Благодаря этому я могу откатить транзакцию назад, если один из составляющих ее операторов потерпит неудачу. Затем я открываю XML-документ функцией OPENXML, начиная обработку с узлов CustomerOrder. В разделе WITH я, в частности, описываю, как считывается CustomerID: выполняется переход на узел XML-документа, являющийся родителем узла Order (т. е. на элемент Customer) и считывается значение атрибута CustomerID этого элемента Customer. Функция OPENXML очень удобна тем, что позволяет, указывая XPath-выражения (с некоторыми ограничениями), перемещаться по XML-документу вверх и вниз и получать значения атрибутов и элементов.
После вставки данных элемента Order я получаю значение OrderID, только что сгенерированное встроенной функцией SQL Server, - SCOPE_IDENTITY. Затем приступаю к вставке записей в таблицу Order Details -
выполняю еще один оператор INSERT, в котором с помощью функции OPENXML из XML-данных считывается информация о позициях заказа. Если не возникнет никаких ошибок, данные о заказе и дочерние данные о позициях заказа будут вставлены в соответствующие таблицы базы данных.
Подход с применением функции SCOPE_IDENTITY работает, только если есть один заказ. Если вставляются данные о нескольких заказах (из нескольких элементов Order и их дочерних элементов), содержащиеся в одном XML-пакете, код усложняется. Проблема в том, что в таблицу Order вставляется несколько записей и нужно сопоставить данным о каждом заказе дочерние данные о позициях этого заказа. Поскольку не известно, к каким родительским записям относятся записи с данными о позициях заказов, придется написать дополнительный код. Один из способов решения проблемы - использовать в разделе WITH метасвойства @mp:id/@mp:parented, позволяющие получить OrderID родителя и сопоставить его полям OrderID потомков.
Вставки и обновления
XML-документы, подготавливаемые SQL Server с помощью системной хранимой процедуры sp_xml_preparedocument, можно указывать в JOIN точно так же, как и любую другую таблицу. И их можно использовать в операторах INSERT, UPDATE или DELETE так же, как и любой другой набор записей. Чтобы продемонстрировать это, я сначала выбрал группу записей таблицы Order Details, имеющих OrderID 10285, и занес их в ADO.NET-объект DataSet. Потом изменил количество в некоторых существующих записях таблицы Order Details и добавил в эту таблицу объекта DataSet несколько новых записей:
DataSet oDs = new DataSet("NorthwindOrderDetailsData");oDa.Fill(oDs, "OrderDetails");// Изменяем данные в 2 позициях заказа (выполняем обновления)oDs.Tables["OrderDetails"].Rows[0]["Quantity"] = "2";oDs.Tables["OrderDetails"].Rows[1]["Quantity"] = "7";// Добавляем еще несколько позиций заказа (выполняем вставки)for (int i = 10; i <= 30; i++) { oDs.Tables["OrderDetails"].Rows.Add( new object[]{iOrderID, i, 1.50, 3, 0});}
Внеся эти изменения, я могу различными способами получить из DataSet измененные данные и преобразовать их в XML-формат. Можно воспользоваться методом WriteXml и записать данные как DiffGram, записать данные вместе со схемой или без схемы, а также получить XML-представление данных методом GetXml. В данном примере я использую режим DiffGram, поскольку тогда XML-данные будут содержать исходное и текущее состояние измененных записей (в случае обновления).
DiffGram передается хранимой процедуре (рис. 7), которая извлекает новые и обновленные записи из XML-документа и вставляет их в переменную типа TABLE (@tblTemp). Заметьте: в вызове функции OPENXML на рис. 7 используется XPath-выражение, выбирающее значение атрибута hasChanges. Для вставленных записей этим значением будет «i», а для обновленных - «m». Поскольку функция OPENXML позволяет фильтровать записи, хранимой процедуре достаточно передать лишь один XML-документ. Затем хранимая процедура вставляет в таблицу Order Details соответствующие записи и обновляет нужные записи Order Details. Операторы INSERT и UPDATE выполняются в транзакции, которую можно откатить назад, если один из них потерпит неудачу.
Массовая вставка с помощью OPENXML
Вставить 10 записей из DataSet, используя DataAdapter и хранимую процедуру, достаточно просто. Но DataAdapter обрабатывает по одной записи DataSet за раз: ищет вставленные записи и выполняет для каждой такой записи хранимую процедуру, заданную в свойстве InsertCommand объекта DataAdapter. Значит, при вставке 10 записей выполняется 10 вызовов хранимой процедуры из базы данных. Такой обмен информацией между кодом для ADO.NET и хранимой процедурой почти не заметен, когда требуется обновить лишь несколько записей. Но при других обстоятельствах, например, когда быстро растет число пользователей, одновременно вставляющих сотни записей, произойдет резкое снижение производительности. Поэтому, вместо того чтобы 100 раз вызывать хранимую процедуру для вставки 100 записей, лучше за один прием передать эти 100 записей как XML-данные.
В примеры кода к статье включено ASP.NET-приложение, которое вызывает хранимую процедуру, вставляющую в таблицу Customers данные об n покупателях. Код для ASP.NET создает и добавляет в DataSet 100 записей с данными о покупателях. Затем вызывается метод WriteXML, который выводит эти записи в XML-формате (без вывода схемы). Полученные XML-данные передаются хранимой процедуре, вставляющей 100 записей за один прием. Такая методика массовой вставки данных эффективнее, чем многократный вызов хранимой процедуры, вставляющей по одной записи о покупателе за раз.
Работа с примерами
Если вы хотите посмотреть с помощью SQL Query Analyzer, как выполняются SQL-запросы с разделами FOR XML, рассмотренные в статье, вам, возможно, придется изменить некоторые параметры, действующие по умолчанию. Например, если вы хотите запрашивать и возвращать XML-данные, нужно прежде всего увеличить максимальное количество символов в столбцах секции результатов (results pane). Выберите Tools / Options / Results и увеличьте значение параметра Maximum Characters per Column, например, до 4096. Поскольку возвращаемые XML-данные показываются в секции результатов как одно поле, они не будут усекаться, как это было бы при количестве символов по умолчанию, равном 256. Благодаря этому вы сможете просматривать XML-данные и, используя какой-либо XML-редактор, копировать их и вставлять в редактор.
Заключение
Введение раздела FOR XML и функции OPENXML - первый шаг на пути интеграции XML и SQL Server. Благодаря этим усовершенствованиям можно полу
чить XML-данные из базы SQL Server, не прибегая к преобразованию данных через промежуточные компоненты. Кроме того, можно передать XML-данные обратно в базу данных, используя функцию OPENXML и старые языковые средства - SQL-операторы INSERT, UPDATE и DELETE. Эти XML-функции - не более чем начало; SQL Server 2005 обеспечит более широкую поддержку XML. Однако они являются действенными средствами, позволяющими уже сегодня интегрировать XML-приложения и SQL Server.
Исходный код можно скачать по ссылке: http://msdn.microsoft.com/msdnmag/code05.aspx.
Вопросы и комментарии (на английском языке) присылайте по адресу mmdata@microsoft.com.
Джон Папа (John Papa) - архитектор .NET и бейсбольный фанат, который проводит большинство летних вечеров вместе с женой, двумя дочками и верным псом Кэди, болея за команду «Yankees». Автор ряда статей и нескольких книг по ADO, XML и SQL Server. Часто выступает на конференциях, в том числе на VSLive. Ведет онлайновый дневник http://codebetter.com/blogs/john.papa.