Использование XML расширений Microsoft SQL Server 2000 и Microsoft расширений стандарта XSLT

Использование XML расширений Microsoft SQL Server 2000 и Microsoft расширений стандарта XSLT

Сначало было слово ...
В настоящий момент современный интернет проходит третью революцию. Первая была, когда появился сам интернет, возможность выкачивания файлов по FTP и обмена почтой в режиме Offline. Вторая пришла с появлением интернет браузеров способных отображать HTML. Третья происходит сейчас и связана с появлением реальной возможности универсального обмена информацией между автоматизированными системами. Речь идет о системах B2B (Business-To-Business). Есть разные способы построения подобных систем, но есть так же и общие проблемы. Одна их них та, что многие Firewall непропускают через себя бинарный траффик (RPC, JRMI и т.д.). Одна из панацей это обмен информацией в формате XML. Вот о некоторых особенностях Microsoft реализации поддержки XML мы о поговорим.

Одно только замечание - эта статья не предполагает обучать XML/XSLT и Microsoft Transact SQL. Ожидается, что читатель знаком с основами XML и XSLT и имеет опыт программирования в Transact SQL.

XML расширения Microsoft SQL Server Transact SQL

Начиная с версии 2000 в Microsoft SQL Server в его диалекте языка SQL - Transact SQL появилась встроенная поддержка XML. Расширения появились как для генерации XML, так и для использования XML в роли источника данных. В этой статье мы поговорим о генерации XML.

Существуют три варианта генерации XML.

В первом варианте каждая результирующая запись представляется отдельным корневым XML элементом с именем "row", а поля записи представляются парами аттрибут="значение". При этом имена результирующих полей не должны повторяться. Обрамляющий корневой элемент отсутствует.

Во втором варианте первая и каждая последующая соединенная таблица (таблица, View, UDF возвращающая тип данных TABLE, именованный подзапрос), представляются в виде дерева соответсвенно вложенных элементов, где имени исходной таблицы соответсвует имя элемента, а значения полей предствалены парами аттрибут="значение", где имя аттрибута соответсвует имени поля. В этом варианте значения полей можно также представить (указав дополнительный параметр запроса) в виде элементов, а не аттрибутов родительского элемента. Обрамляющий корневой элемент также отсутствует.

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

Во всех случаях, для генерации XML используется конструкция FOR XML ... .

Приведем примеры. Все примеры приведены для поставляющейся вместе с Microsoft SQL Server 2000 учебной базы данных Northwind:

Во всех трех примерах мы хотим узнать одно и тоже - когда делались заказы двух интересующих нас фирм.

Вариант 1 (FOR XML RAW).

Текст запроса:

SELECT
  Customers.ContactName,
  Customers.CompanyName,
  Orders.OrderDate
FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID IN ('LAZYK', 'LAUGB')
ORDER BY Customers.CompanyName
FOR XML RAW


Результат запроса*:

<row ContactName="Yoshi Tannamuri" CompanyName="Laughing Bacchus Wine Cellars" OrderDate="1997-04-03T00:00:00"/>
<row ContactName="Yoshi Tannamuri" CompanyName="Laughing Bacchus Wine Cellars" OrderDate="1997-08-05T00:00:00"/>
<row ContactName="Yoshi Tannamuri" CompanyName="Laughing Bacchus Wine Cellars" OrderDate="1998-01-01T00:00:00"/>
<row ContactName="John Steel" CompanyName="Lazy K Kountry Store" OrderDate="1997-03-21T00:00:00"/>
<row ContactName="John Steel" CompanyName="Lazy K Kountry Store" OrderDate="1997-05-22T00:00:00"/>

* - на самом деле весь результат возвращается в виде одной длинной строки разбитой на кусочки (отдельные записи) длинной не более 8000 байт. Для использования результата необходимо сначала "склеить" все кусочки в одну длинную строку, и за тем отдать эту строку на обработку парсеру. При этом, если используется вариант генерации XML без общего корневого элемента, то его (корневой элемент) придется добавить к полученной строке самому (открывающую конструкцию к началу строки, закрывающую к концу). В следующую версию этой статьи предполагается добавить раздел с примерами о том, как автоматически подготавливать полученный результат для использования.

Вариант 2 (FOR XML AUTO).

Текст запроса:

SELECT
  Customers.ContactName,
  Customers.CompanyName,
  Orders.OrderDate
FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID IN ('LAZYK', 'LAUGB')
ORDER BY Customers.CompanyName
FOR XML AUTO

Результат запроса:

<Customers ContactName="Yoshi Tannamuri" CompanyName="Laughing Bacchus Wine Cellars">
  <Orders OrderDate="1997-04-03T00:00:00"/>
  <Orders OrderDate="1997-08-05T00:00:00"/>
  <Orders OrderDate="1998-01-01T00:00:00"/>
</Customers>
<Customers ContactName="John Steel" CompanyName="Lazy K Kountry Store">
  <Orders OrderDate="1997-03-21T00:00:00"/>
  <Orders OrderDate="1997-05-22T00:00:00"/>
</Customers>

Вариант 3 (FOR XML EXPLICIT).

Результат запроса - обратите внимание на то, что, во первых, присутствует корневой элемент, во вторых, значение ContactName представлено в виде аттрибута, а CompanyName в виде элемента:

<CustomerReport>
  <Customer ContactName="Yoshi Tannamuri">
    <CompanyName>Laughing Bacchus Wine Cellars</CompanyName>
    <Order Date="1997-04-03T00:00:00"/>
    <Order Date="1997-08-05T00:00:00"/>
    <Order Date="1998-01-01T00:00:00"/>
  </Customer>
  <Customer ContactName="John Steel">
    <CompanyName>Lazy K Kountry Store</CompanyName>
    <Order Date="1997-03-21T00:00:00"/>
    <Order Date="1997-05-22T00:00:00"/>
  </Customer>
</CustomerReport>

Текст запроса:

SELECT
  Tag = 1,
  Parent = NULL,
  [CustomerReport!1] = NULL,
  [Customer!2!ContactName] = NULL,
  [Customer!2!CompanyName!element] = NULL,
  [Order!3!Date] = NULL
UNION ALL
SELECT
  Tag = 2,
  Parent = 1,
  [CustomerReport!1] = NULL,
  [Customer!2!ContactName] = Customers.ContactName,
  [Customer!2!CompanyName!element] = Customers.CompanyName,
  [Order!3!Date] = NULL
FROM Customers
WHERE Customers.CustomerID IN ('LAZYK', 'LAUGB')
UNION ALL
SELECT
  Tag = 3,
  Parent = 2,
  [CustomerReport!1] = NULL,
  [Customer!2!ContactName] = NULL,
  [Customer!2!CompanyName!element] = Customers.CompanyName,
  [Order!3!Date] = Orders.OrderDate
FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID IN ('LAZYK', 'LAUGB')
ORDER BY [Customer!2!CompanyName!element], [Order!3!Date]
FOR XML EXPLICIT

FOR XML RAW [, XMLDATA] [, BINARY BASE64]

FOR XML AUTO [, XMLDATA] [, ELEMENTS][, BINARY BASE64]

FOR XML EXPLICIT [, XMLDATA] [, ELEMENTS][, BINARY BASE64]

XMLDATA
Указывает, что должна быть также возвращена схема данных в формате Microsoft XML-Data Schema. Схема добавляется к результирующему документу как встроенная (inline) схема.

ELEMENTS
Если указан параметр ELEMENTS, то поля таблиц будут возвращены как вложенные XML элементы. Эта возможность поддерживается в XML AUTO варианте.

BINARY BASE64
Если указан этот параметр, то BINARY данные будут возвращены в Base64-encoded виде.

Теперь немного о том, что это за жуткие имена полей в варианте XML EXPLICIT и почему так много UNION запросов? Первым полем каждого запроса идет "Tag" со значение порядкового номера элемента. Дело в том, что в варианте XML EXPLICIT каждый новый (по имени или уровню вложенности) элемент должен иметь сугубо внутренний, но при этом уникальный цифровой идентификатор.

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

Все остальные поля, это конструкции [ИмяЭлемента!НомерЭлемента!Аттрибут]. В случае если вы хотите представить значение не в виде аттрибута, а в виде вложенного элемента, то достаточно добавить ключевое слово element. Т.е. [ИмяЭлемента!НомерЭлемента!Аттрибут!element]. При этом значения поле должны присваиваться в соответсвующих объединенных UNION запросах. Это значит, если если вы хотите присвоит значение полю [ElementDDD!3!AttributeKKK], то это должно быть произведено в запросе с Tag=3.

Вот вроде и все. На последок несколько "тонких" моментов:

1. Очень важно соблюсти порядок сортировки записей. Иначе SQL Server если и не сгенерит сообщение об ошибке, то произведет на свет такой XML, который не только не будет отвечать желаемой вами структуре но и вызовет ошибку парсинга. Здесь нет универсального рецепта. Скажу одно, что записи должны идти в том порядке, в каком расположены XML элементы. Т.е. как они вам визуално попадаются при просмотре сверху в низ, так и записи должны идти.
2. Чтобы сократить количество запросов моделируйте структуру вашего XML дерева как можно проще и унивесальнее.

Microsoft расширения стандарта XSLT

Речь идет о XSLT конструкциях <msxsl:node-set(...)> и <msxsl:script ...>

Первая конструкция - <msxsl:node-set(...)> - была введена Microsoft в ответ на исчезновение из синтаксиса XSLT (по сравнению с XSL) возможности использования в виде XML источника XML текста сохраненного в переменной (<xsl:variable ...>). Теперь достаточно обрамить эту переменную в конструкцию msxsl:node-set($MyXMLText) и разбирать ее обычными средствами:

<xsl:variable name="MyXMLText"> 
	<Person>
		<FirstName>Yuri</FirstName> 
		<SecondName>Abele</SecondName> 
	<Person> 
	<Person>
		<FirstName>Vasja</FirstName> 
		<SecondName>Pupkin</SecondName> 
	<Person> 
</xsl:variable>

<table>
<xsl:for-each select="msxsl:node-set($MyXMLText)/Person"> 
	<tr>
		<td><xsl:value-of select="FirstName" /></td>
		<td><xsl:value-of select="SecondName" /></td>
	</tr>
</xsl:for-each>
</table>

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

Приведем пример. Вот в частности JScript функция, которая получает на вход первым параметром Pattern текста SQL запроса а всеми остальными параметрами заполняет этот Pattern.

После этого она выполняет это SQL запрос (он должен возвращать FOR XML EXPLICIT результат) и возвращает результат в виде XMLDOMNodeList.

Теперь с этим результатом можно работать как с простым XML - т.е. перебирать/анализировать его средствами XSLT Functions & Xpath

1. Создадим файл с именем XSLTExtensions.XSL
2. В заголовочную часть поместим два необходимых NameSpace:

   <?xml version="1.0" encoding="iso-8859-1" ?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:user-namespace-here" xml:space="default" version="1.0"> <xsl:output method="html" encoding="iso-8859-1" />

3. Далее создадим блок

   <msxsl:script language="JScript" implements-prefix="user"><![CDATA[ ]]></msxsl:script>

4. В тело этого блока вставим нашу функцию (функций может быть сколько угодно) - см. Листинг 1.
5. Теперь достаточно в тот XSLT файл, где мы будем использовать нашу функцию вставить ссылку на файл с функцией:

   <xsl:include href="http://www.server.xx/Library/XSL/Extensions.XSL" />

6. Вызываются такие функции следующим образом

   <xsl:for-each select="user:SQLXML('EXEC StoredProcedureXML @ID = ?', /MyXML/User/@ID)/ResultXML/SubTree/"> <xsl:value-of select="Element/@Attribute" /><br /> </xsl:for-each>

   В этом примере хранимая процедура StoredProcedureXML возвращает XML дерево вида

   <ResultXML> <SubTree> <Element Attribute="111" /> <Element Attribute="222" /> <Element Attribute="333" /> <Element Attribute="444" /> </SubTree> <OtherElement /> </ResultXML>

О том, как использовать XML расширения TransactSQL в Microsoft SQLServer 2000 говорится в предыдущем разделе.

Листинг 1
function SQLXML(str_Query)
{
	var con_Connection = new ActiveXObject("ADODB.Connection");
	var rs_RecordSet = new ActiveXObject("ADODB.RecordSet");
	var obj_XMLSource = new ActiveXObject("Microsoft.XMLDOM");
	var str_XML = new String("<?xml version=\"1.0\" encoding=\"iso-8859-1\" ?>\n" +
				"<!-- Ececution Time: " + (new Date()).toString() + "-->\n");
	var int_I = new Number(0);

	con_Connection.CursorLocation = 3;	// adUseClient 
	con_Connection.Mode = 1;		// adModeRead

	con_Connection.Open("My_ODBC_DSN");

	while (String(str_Query).search("`") > 0)
		str_Query = String(str_Query).replace("`", "'");

	for (int_I = 1; int_I < arguments.length; int_I++)
	{
		switch (typeof(arguments[int_I]))
		{
			case String("object"):
				str_Query = String(str_Query).replace("?",
				 	String(arguments[int_I].nextNode().nodeTypedValue));
				break;
			default:
				str_Query = String(str_Query).replace("?",
						String(arguments[int_I]));
				break;
		}
	}

	rs_RecordSet = con_Connection.Execute(str_Query);
	while(rs_RecordSet.EOF != true)
	{
		str_XML = str_XML + rs_RecordSet.Fields.Item(0);
		rs_RecordSet.MoveNext;
	}

	rs_RecordSet.Close();
	rs_RecordSet = null;

	con_Connection.Close();
	con_Connection = null;

	obj_XMLSource.async = false;
	obj_XMLSource.loadXML(str_XML);

	if (obj_XMLSource.parseError.errorCode != 0)
	{ 
		str_Result = _XMLTransformer_ReportParseError(obj_XMLSource.parseError, "XML Source Loading Error");
		obj_XMLSource.loadXML(str_Result);
	}

	return obj_XMLSource;
}

В следующих редакциях статьи будут освещены следующие темы:

1. Расширения Transact SQL позволяющие использовать XML как источник данных.
2. Получение результатов FOR XML запросов и автоматическая подготовка их к использованию (на примере Microsoft ASP).
3. SQL XML Support in IIS - возможность позволяющая слать SQL Server SQL запросы в коммандной строке интерент браузера и получать результат в XML или трансформированом в HTML виде.