XML в MS SQL Server 2000 и технологиях доступа к данным - XML на стороне сервера. FOR XML
ОГЛАВЛЕНИЕ
XML на стороне сервера. FOR XML
Следующая ступень эволюции - ADO 2.6 и SQL Server 2000. В SQL Server 2000 в синтаксис Т-SQL был добавлен предикат FOR XML для оператора SELECT, что позволило получать XML-текст как результаты запроса на стороне сервера. Рассмотрим запрос SELECT c.ContactName, c.ContactTitle, o.OrderDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID FOR XML AUTO. Вначале SQL Server традиционным способом выполняет ту часть запроса, которая находится до FOR XML. Затем к полученному множеству записей сервер применяет преобразование в XML. Если выполнить этот запрос из Query Analyzer, то видно, что содержимое XML-документа разбито по записям длиной 2033 символа Unicode одноколоночного recordset'а. Вообще говоря, это не есть ни recordset, ни XML. Его нельзя использовать как результат подзапросов, хранимых функций и всего остального, что предполагает дальнейшую обработку на SQL Server. Это нечто предназначено только для передачи клиенту, где из него уже происходит сборка полноценного документа. Таким образом, несмотря на то, что в отличие от Скриптов 1 и 2, в Скрипте 3 XML фактически получается на сервере, все XPath-, updategrams- и прочие запросы выполняются на клиенте, поскольку встроенный тип XML в настоящее время в SQL Server отсутствует.
{
...
ADODB.StreamClass str = new ADODB.StreamClass();
str.Open(System.Type.Missing, ADODB.ConnectModeEnum.adModeUnknown, ADODB.StreamOpenOptionsEnum.adOpenStreamUnspecified, "", "");
ADODB.CommandClass cmd = new ADODB.CommandClass();
cmd.ActiveConnection = cnn;
cmd.CommandText = "SELECT '<Root>' " +
"SELECT c.ContactName, c.ContactTitle, o.OrderDate FROM Customers c " +
"INNER JOIN Orders o ON c.CustomerID = o.CustomerID " +
"WHERE c.ContactName = ? AND year(o.OrderDate) = ? FOR XML AUTO " +
"SELECT '</Root>'"; cmd.Parameters.Append(cmd.CreateParameter("@Name", ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, 30, null));
cmd.Parameters.Append(cmd.CreateParameter("@Year", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 4, null));
cmd.Dialect = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}";
cmd.Properties["Output Stream"].Value = str;
cmd.Properties["Output Encoding"].Value = "UTF-8";
object RecsAffected = null, Params = new object[] {"Maria Larsson", 1997};
cmd.Execute(out RecsAffected, ref Params, (int)ADODB.ExecuteOptionEnum.adExecuteStream);
MSXML2.DOMDocument40Class xmlDoc = new MSXML2.DOMDocument40Class();
xmlDoc.load(str);
}
Скрипт 3 выполняет тот же запрос, что и в предыдущих примерах, за исключением того, что я его слегка разнообразил передачей параметров: он показывает список всех заказов, сделанных определенным клиентом за определенный год. Результат заключается в скобки <Root> … </Root> для получения well-formed документа. Вместо этого можно использовать cmd.Properties["xml root"].Value = "Root". XML возвращается на клиента в объекте ADODB.Stream. Его можно сохранить сразу в файл - str.SaveToFile(f.FullName, ADODB.SaveOptionsEnum.adSaveCreateOverWrite); , а можно передать как поток в документ типа MSXML2.DOMDocument40Class. В любом случае понятно, что над результатами запроса можно вести дальнейшую работу средствами DOM. Аналогично Скрипту 2 для работы с XML-документами здесь используется библиотека СОМ, а не .NET, поскольку ADODB.Stream нельзя преобразовать к System.IO.Stream, чтобы загрузить в System.Xml.XmlDocument. Передавать же, сохраняя в промежуточный файл, как делалось в Скрипте 1, можно, но неизящно. Свойство Dialect класса ADODB.Command говорит провайдеру, какой тип команды используется. Возможные значения приведены в Табл.1
| Тип команды | Значение в ADO | Константа в OLE DB |
| Запрос Transact-SQL | {C8B522D7-5CF3-11CE-ADE5-00AA0044773D} | DBGUID_SQL |
| Запрос XPath | {EC2A4293-E898-11D2-B1B7-00C04F680C56} | DBGUID_XPATH |
| Запрос в XML-шаблоне | {5D531CB2-E6Ed-11D2-B252-00C04F681B71} | DBGUID_MSSQLXML |
| Поведение провайдера по умолчанию | {C8B521FB-5CF3-11CE-ADE5-00AA0044773D} | DBGUID_DEFAULT |
Табл.1
Объекты типа Stream появились в ADO 2.5 для поддержки некоторых типов нереляционной информации. Вспомним, что первоначальная спецификация OLE DB в 1996 г. описывала универсальный доступ в рамках прямоугольных recordset'ов для обеспечения совместимости с ранними API, имевшими дело с реляционными данными (ODBC, DB-Library, DAO). Однако несмотря на развитость аппарата реляционной алгебры далеко не все источники удавалось свести к этому классу. Да и потом, иерархические базы данных все-таки предшествовали реляционным, поскольку эта модель, видимо, более естественно отвечает образу мышления. Время шло, диссонанс между объектно-ориентированной средой разработки и реляционной архитектурой хранения (плоские таблицы, связанные отношениями) проявлялся все более отчетливо. В ADO 1.5 была сделана попытка сгладить эту проблему с помощью провайдера MSDataShape.
Спецификация OLE DB 1.5 предусматривала новый тип полей - Chapter column. Предполагалось, что индивидуальные уровни иерархии можно представить в виде отдельных rowset'ов, и chapter указывает для родительской записи множество ее детей в дочернем rowset'e. Однако все это было хорошо в однородных иерархиях, когда дочерняя запись имеет тот же набор полей, что и родительская. Например, очевидный негомогенный источник - файловая система - сюда уже не вписывается. Чтобы уложить древовидную структуру в прямоугольную с минимальными переделками и потерями производительности (скажем, не заводя для каждой записи число полей, соответствующее полному набору всех возможных атрибутов узла в дереве, большая часть из которых будет, очевидно, пустовать) в ADO 2.5 были введены классы Record и Stream. Набор полей (атрибутов) записи (файла) может разниться не только с родительской записью (папкой), но и меняться от записи к записи (например, в зависимости от типа файла). Наименьший общий знаменатель полей, присущих всем записям, формировал колонки привычного Recordset. Класс Stream соответствовал содержанию файла. Таким образом, появилась возможность получения результата запроса в виде потoка, чем мы и воспользовались в Скрипте 3. XML естественным образом решает проблему представления древовидных иерархий. Как и RDS, MSDataShape в настоящее время поддерживается по соображениям совместимости, но развиваться в дальнейшем не будет.
Для предиката FOR XML существует три возможных опции: AUTO, RAW и EXPLICIT. Действие первой мы уже видели. Она дает SQL Server указание сформировать простое вложенное дерево, где иерархия (вложенность) определяется порядком связывания таблиц в запросе. Каждой таблице из FROM, хотя бы одно поле которой попадает в вывод, ставится в соответствие элемент, имя которого равно имени / псевдониму таблицы. Поля таблиц отвечают атрибутам элементов. Отсюда следует, что все поля в запросе должны быть поименованы. Недопустимо, например, SELECT 1 FOR XML… Опция RAW формирует плоский неиерархический документ независимо от отношений между таблицами в запросе. Он подобен тому, что мы видели на рис.1. Каждая запись результата соответствует элементу с именем row. Опция EXPLICIT наиболее гибкая из трех и позволяет получить XML произвольной структуры, однако recordset, по которому он строится, должен следовать определенным правилам расположения записей и именования полей, чтобы однозначно задать желаемую структуру дерева. Я не буду сейчас подробно расписывать эти правила, т.к. несмотря на гибкость, способ этот достаточно громоздок и на практике применяется редко. В основном он используется самим SQL Server'ом для преобразования реляционной структуры к аннотированной схеме (см.п.9). Подробно узнать про опцию EXPLICIT можно в документации на SQL Server (см. XML and Internet Support -> Using EXPLICIT Mode).
Поддерживаются параметризованные запросы с FOR XML и процедуры, возвращающие SELECT … FOR XML. Передача параметров осуществляется стандартно при помощи коллекции Parameters объекта ADODB.Command. Поля типа text / ntext возвращаются в виде текста, поля типа image - в виде их XPath-пути. FOR XML …, Binary Base64 возвращает их в кодировке Base64 и для опции RAW это единственный возможный способ вывести значения BLOB-типов. FOR XML AUTO, ELEMENTS отображает поля не на атрибуты, а на подэлементы. SELECT TOP 0 ... FOR XML AUTO, XMLData дает схему XML-результата в формате XDR. Начиная с SQLXML 2.0 включена поддержка XSD-схем и утилита для конвертации XDR в XSD (cvtschema.ехе).