XML+MSSQL+ASP.NET. Часть 1.

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

Но, исходя из того, что наш сайт имеет направление на популяризацию тех или иных технологий (решений) и все материалы основываются на нашем собственном опыте, мы пришли к мнению, что надо изложить те результаты работы с XML-данными, которые у нас получались лучше всего. Если и есть неточности в изложении материала, то в отношении работоспособности примеров сомневаться не приходится. Все замечания приветствуются и принимаются на электронную почту qstart@narod.ru

Сам по себе XML примитивен. Начальная строка <?xml version="1.0"?>, корневой элемент (не обязательно <root>, любое имя), с обязательным закрывающим элементом, а так же вложенные элементы (теги) с атрибутами и значениями. Любой структуры, любой вложенности. И всё. Кратчайшее описание языка укладывается в три строки. Но на этот простейший язык разметки документов накручено очень много сложных технологий. Языки XPath (поиск и отбор узлов) и XSLT (трансформация документов), Web-сервисы (SOAP), MSSQL (хранение данных в XML-формате в MSSQL2005, представление результатов запросов в XML - в MSSQL2000) и многое другое.

Итак, связка ASP.NET, MSSQL2000 и XML. Разрабатывать эту тему нас заставила задача вывести для пользователя в браузер базу данных из более чем 6 тысяч строк. В классическом ASP, в цикле, вывод таблицы такого размера занимал около 5-7 минут. Для пользователя этот время никак не годилось. И вот тут оказалось, что вывод данных из MSSQL в XML виде занимает считанные секунды. Оставалось только преобразовать XML-поток в таблицу, так пришлось осваивать шаблонизацию на основе XSLT. Это предыстория вопроса, которая была решена средствами классического ASP.

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

Для использования всей функциональности необходимо на сервере (у нас SQL и IIS установлены на одной машине) инсталлировать SqlXml 3.0 Service Pack 3 (SP3). Скачать его можно по ссылке http://www.microsoft.com/downloads/details.aspx?familyid=51D4A154-8E23-47D2-A033-764259CFB53B&displaylang=en. На этой странице так же кратко описано для чего предназначен данный продукт (на английском).

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

После установки этого продукта на компьютере в Program Files появится раздел "SQLXML 3.0", в котором есть CHM-файл с документацией и примерами использования.

Напишем первую программу (файл с расширением aspx)

<%@ Page Language="VB" %>
<%@ import Namespace="Microsoft.Data.SqlXML" %>
<script runat="server">
      Sub Page_Load(sender As Object, e As EventArgs)
         Dim cmd as SqlXmlCommand
         cmd = New SqlXmlCommand("Provider=SQLOLEDB;server=(local);database=pubs;uid=sa;password=sa")
         cmd.CommandText ="SELECT * FROM Titles FOR XML AUTO"
         Response.ContentType = "text/xml"
         cmd.RootTag = "root"
         Response.Clear()
         cmd.ExecuteToStream(Response.OutputStream)
      End SUb
</script>

Файл сохраним как primer.aspx (в каталоге wwwroot) и в строке адреса браузере наберем http://localhost/primer.aspx

Если запущен IIS, NET Framework установлен, MSSQL функционирует, то получим следующую картинку (результаты сокращены)

<?xml version="1.0" encoding="utf-8" ?>
  <root>
	  <Titles title_id="BU1032" title="The Busy Executive's Database Guide" type="business" pub_id="1389" price="19.99" advance="5000" royalty ="10" ytd_sales="4095" notes="An overview of available database systems with emphasis on common business applications. Illustrated." pubdate="1991-06-12T00:00:00" />
	  <Titles title_id="BU1111" title="Cooking with Computers: Surreptitious Balance Sheets" type="business" pub_id="1389" price="11.95" advance="5000" royalty="10" ytd_sales="3876" notes="Helpful hints on how to use your electronic resources to the best advantage." pubdate="1991-06-09T00:00:00" />
	  <Titles title_id="BU2075" title="You Can Combat Computer Stress!" type="business" pub_id="0736" price="2.99" advance="10125" royalty ="24" ytd_sales="18722" notes="The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations." pubdate="1991-06-30T00:00:00" />
  </root>

Данный вид документа получается по режиму FOR XML AUTO. Имена строк одинаковы с названием таблицы, значения полей записей представлены как атрибуты.

Для того, чтобы атрибуты превратить во вложенные элементы воспользуемся опцией ELEMENTS. Запрос

  cmd.CommandText ="SELECT * FROM Titles FOR XML AUTO, ELEMENTS"

превращает атрибуты <titles> в самостоятельные элементы. Результаты выполнения запроса сокращены.

<?xml version="1.0" encoding="utf-8" ?> 
  <root>
	<Titles>
		  <title_id>BU1032</title_id> 
		  <title>The Busy Executive's Database Guide</title>
		  <type>business</type> 
		  <pub_id>1389</pub_id> 
		  <price>19.99</price> 
		  <advance>5000</advance> 
		  <royalty>10</royalty> 
		  <ytd_sales>4095</ytd_sales> 
		  <notes>An overview of available database systems with emphasis on common business applications. Illustrated.</notes> 
		  <pubdate>1991-06-12T00:00:00</pubdate>
	</Titles>
	<Titles>
		  <title_id>BU1111</title_id> 
		  <title>Cooking with Computers: Surreptitious Balance Sheets</title> 
		  <type>business</type> 
		  <pub_id>1389</pub_id> 
		  <price>11.95</price> 
		  <advance>5000</advance> 
		  <royalty>10</royalty> 
		  <ytd_sales>3876</ytd_sales> 
		  <notes>Helpful hints on how to use your electronic gt;resources to the best advantage.</notes> 
		  <pubdate>1991-06-09T00:00:00</pubdate>
	</Titles>
	<Titles>
		  <title_id>BU2075</title_id> 
		  <title>You Can Combat Computer Stress!</title> 
		  <type>business</type> 
		  <pub_id>0736</pub_id> 
		  <price>2.99</price> 
		  <advance>10125</advance> 
		  <royalty>24</royalty> 
		  <ytd_sales>18722</ytd_sales> 
		  <notes>The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations.</notes> 
		  <pubdate>1991-06-30T00:00:00</pubdate>
	</Titles>
  </root>

Но самая интересная возможность - это режим SELECT'а - FOR XML EXPLICIT. Эта конструкция позволяет формировать XML-данные в той структуре (иерархии), в которой необходимо вам. Данный режим позволяет установить полный контроль над структурой XML-документа, но плата за это - более сложное использование.

Структура выходного документа описывается в терминах универсальной таблицы. Универсальная таблица - это представление результирующего набора данных со специальными заголовками столбцов, которые говорят SQL-серверу, как создавать документ. Инструкция обязательно начинается с SELECT 1 AS Tag, NULL AS Parent. Далее перечисляются поля таблицы и их имена по специальному формату Element!Tag!Attribute!Directive

Далее я не буду углубляться в дебри, а постараюсь проиллюстрировать на примерах. Тем более, что объяснить теорию использования этой конструкции лучше чем Кен Хендерсон я не смогу. Координаты книги этого автора ("Профессиональное руководство по SQL Server:") я приведу в конце статьи.

  cmd.CommandText = "select 1 AS Tag, null as Parent, pub_id AS [Книга!1!Издатель], title AS [Книга!1!Название],price AS [Книга!1!Цена], ytd_sales AS [Книга!1!Продажи] FROM titles FOR XML EXPLICIT"

Получаем

  <?xml version="1.0" encoding="utf-8" ?> 
  <root>
	  <Книга Издатель="1389" Название="The Busy Executive's Database Guide" Цена="19.99" Продажи="4095" /> 
	  <Книга Издатель="1389" Название="Cooking with Computers: Surreptitious Balance Sheets" Цена="11.95" Продажи="3876" /> 
	  <Книга Издатель="0736" Название="You Can Combat Computer Stress!" Цена="2.99" Продажи="18722" /> 
	  <Книга Издатель="1389" Название="Straight Talk About Computers" Цена="19.99" Продажи="4095" /> 
	  <Книга Издатель="0877" Название="Silicon Valley Gastronomic Treats" Цена="19.99" Продажи="2032" /> 
	  <Книга Издатель="0877" Название="The Gourmet Microwave" Цена="2.99" Продажи="22246" /> 
	  <Книга Издатель="0877" Название="The Psychology of Computer Cooking" /> 
	  <Книга Издатель="1389" Название="But Is It User Friendly?" Цена="22.95" Продажи="8780" /> 
	  <Книга Издатель="1389" Название="Secrets of Silicon Valley" Цена="20" Продажи="4095" /> 
	  <Книга Издатель="1389" Название="Net Etiquette" /> 
	  <Книга Издатель="0877" Название="Computer Phobic AND Non-Phobic Individuals: Behavior Variations" Цена="21.59" Продажи="375" /> 
	  <Книга Издатель="0736" Название="Is Anger the Enemy?" Цена="10.95" Продажи="2045" /> 
	  <Книга Издатель="0736" Название="Life Without Fear" Цена="7" Продажи="111" /> 
	  <Книга Издатель="0736" Название="Prolonged Data Deprivation: Four Case Studies" Цена="19.99" Продажи="4072" /> 
	  <Книга Издатель="0736" Название="Emotional Security: A New Algorithm" Цена="7.99" Продажи="3336" /> 
	  <Книга Издатель="0877" Название="Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean" Цена="20.95" Продажи="375" /> 
	  <Книга Издатель="0877" Название="Fifty Years in Buckingham Palace Kitchens" Цена="11.95" Продажи="15096" /> 
	  <Книга Издатель="0877" Название="Sushi, Anyone?" Цена="14.99" Продажи="4095" /> 
  </root>

Пока ничего особенного, не считая названий элементов и атрибутов по-русски.

Если мы захотим превратить атрибуты в самостоятельные элементы, то превращение организовывается очень просто. К имени столбца добавляется Directive - Element, т.е. [Row!1!Название!Element]

  cmd.CommandText = "select 1 AS Tag, null as Parent, pub_id AS [Книга!1!Издатель], title AS [Книга!1!Название!Element],price AS [Книга!1!Цена!Element], ytd_sales AS [Книга!1!Продажи!Element] FROM titles FOR XML EXPLICIT"

  <?xml version="1.0" encoding="utf-8" ?> 
  <root>
	  <Книга Издатель="1389">
		  <Название>The Busy Executive's Database Guide</Название> 
		  <Цена>19.99</Цена> 
		  <Продажи>4095</Продажи> 
	  </Книга>
	  <Книга Издатель="1389">
		  <Название>Cooking with Computers: Surreptitious Balance Sheets</Название> 
		  <Цена>11.95</Цена> 
		  <Продажи>3876</Продажи> 
	  </Книга>
	  <Книга Издатель="0736">
		  <Название>You Can Combat Computer Stress!</Название> 
		  <Цена>2.99</Цена> 
		  <Продажи>18722</Продажи> 
	  </Книга>
	  <Книга Издатель="1389">
		  <Название>Straight Talk About Computers</Название> 
		  <Цена>19.99</Цена> 
		  <Продажи>4095</Продажи> 
	  </Книга>
	  <Книга Издатель="0877">
		  <Название>Silicon Valley Gastronomic Treats</Название> 
		  <Цена>19.99</Цена> 
		  <Продажи>2032</Продажи> 
	  </Книга>
  </root>

Давайте усложним задачу. К примеру, мы хотим объединить записи по идентификатору издателя (pub_id), т.е. собрать все издания для каждого pub_id и вывести их как подчиненные своему идентификатору. В таком запросе надо объединить таблицу саму с собой, по полю pub_id, установив при этом иерархическую взаимосвязь между элементами.

  cmd.CommandText = "select 1 AS Tag, null as Parent, pub_id AS [Издатель!1!Номер], NULL AS [Книга!2!Название!Element],NULL AS [Книга!2!Цена!Element], NULL AS [Книга!2!Продажи!Element] from titles UNION SELECT 2 AS Tag, 1 AS Parent, pub_id, title,price, ytd_sales FROM titles ORDER BY [Издатель!1!Номер] FOR XML EXPLICIT"

  <?xml version="1.0" encoding="utf-8" ?> 
  <root>
	  <Издатель Номер="0736">
		  <Книга>
			  <Название>Emotional Security: A New Algorithm</Название> 
			  <Цена>7.99</Цена> 
			  <Продажи>3336</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>Is Anger the Enemy?</Название> 
			  <Цена>10.95</Цена> 
			  <Продажи>2045</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>Life Without Fear</Название> 
			  <Цена>7</Цена> 
			  <Продажи>111</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>Prolonged Data Deprivation: Four Case Studies</Название> 
			  <Цена>19.99</Цена> 
			  <Продажи>4072</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>You Can Combat Computer Stress!</Название> 
			  <Цена>2.99</Цена> 
			  <Продажи>18722</Продажи> 
		  </Книга>
	  </Издатель>
	  <Издатель Номер="0877">
		  <Книга>
			  <Название>Computer Phobic AND Non-Phobic Individuals: Behavior Variations</Название> 
			  <Цена>21.59</Цена> 
			  <Продажи>375</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>Fifty Years in Buckingham Palace Kitchens</Название> 
			  <Цена>11.95</Цена> 
			  <Продажи>15096</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean</Название> 
			  <Цена>20.95</Цена> 
			  <Продажи>375</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>Silicon Valley Gastronomic Treats</Название> 
			  <Цена>19.99</Цена> 
			  <Продажи>2032</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>Sushi, Anyone?</Название> 
			  <Цена>14.99</Цена> 
			  <Продажи>4095</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>The Gourmet Microwave</Название> 
			  <Цена>2.99</Цена> 
			  <Продажи>22246</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>The Psychology of Computer Cooking</Название> 
		  </Книга>
	  </Издатель>
	  <Издатель Номер="1389">
		  <Книга>
			  <Название>But Is It User Friendly?</Название> 
			  <Цена>22.95</Цена> 
			  <Продажи>8780</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>Cooking with Computers: Surreptitious Balance Sheets</Название> 
			  <Цена>11.95</Цена> 
			  <Продажи>3876</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>Net Etiquette</Название> 
		  </Книга>
		  <Книга>
			  <Название>Secrets of Silicon Valley</Название> 
			  <Цена>20</Цена> 
			  <Продажи>4095</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>Straight Talk About Computers</Название> 
			  <Цена>19.99</Цена> 
			  <Продажи>4095</Продажи> 
		  </Книга>
		  <Книга>
			  <Название>The Busy Executive's Database Guide</Название> 
			  <Цена>19.99</Цена> 
			  <Продажи>4095</Продажи> 
		  </Книга>
	  </Издатель>
  </root>

Представление данных не очень удобно для восприятия, но для обработки анализатором XML не имеет значения, какая структура у документа. Давайте изменим запрос так, что бы перевести подчиненные элементы у <Книга> в атрибуты. Для этого просто уберем из запроса директиву Element.

  cmd.CommandText = "select 1 AS Tag, null as Parent, pub_id AS [Издатель!1!Номер], NULL AS [Книга!2!Название],NULL AS [Книга!2!Цена], NULL AS [Книга!2!Продажи] from titles UNION SELECT 2 AS Tag,1 AS Parent, pub_id, title,price, ytd_sales FROM titles ORDER BY [Издатель!1!Номер] FOR XML EXPLICIT"

Результат на экране:

  <?xml version="1.0" encoding="utf-8" ?> 
  <root>
	  <Издатель Номер="0736">
		  <Книга Название="Emotional Security: A New Algorithm" Цена="7.99" Продажи="3336" /> 
		  <Книга Название="Is Anger the Enemy?" Цена="10.95" Продажи="2045" /> 
		  <Книга Название="Life Without Fear" Цена="7" Продажи="111" /> 
		  <Книга Название="Prolonged Data Deprivation: Four Case Studies" Цена="19.99" Продажи="4072" /> 
		  <Книга Название="You Can Combat Computer Stress!" Цена="2.99" Продажи="18722" /> 
	  </Издатель>
	  <Издатель Номер="0877">
		  <Книга Название="Computer Phobic AND Non-Phobic Individuals: Behavior Variations" Цена="21.59" Продажи="375" /> 
		  <Книга Название="Fifty Years in Buckingham Palace Kitchens" Цена="11.95" Продажи="15096" /> 
		  <Книга Название="Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean" Цена="20.95" Продажи="375" /> 
		  <Книга Название="Silicon Valley Gastronomic Treats" Цена="19.99" Продажи="2032" /> 
		  <Книга Название="Sushi, Anyone?" Цена="14.99" Продажи="4095" /> 
		  <Книга Название="The Gourmet Microwave" Цена="2.99" Продажи="22246" /> 
		  <Книга Название="The Psychology of Computer Cooking" /> 
	  </Издатель>
	  <Издатель Номер="1389">
		  <Книга Название="But Is It User Friendly?" Цена="22.95" Продажи="8780" /> 
		  <Книга Название="Cooking with Computers: Surreptitious Balance Sheets" Цена="11.95" Продажи="3876" /> 
		  <Книга Название="Net Etiquette" /> 
		  <Книга Название="Secrets of Silicon Valley" Цена="20" Продажи="4095" /> 
		  <Книга Название="Straight Talk About Computers" Цена="19.99" Продажи="4095" /> 
		  <Книга Название="The Busy Executive's Database Guide" Цена="19.99" Продажи="4095" /> 
	  </Издатель>
  </root>

В следующей части статьи, покажем как преобразовывать такого рода данные к виду HTML, псевдо-Excel (SpreadSheet) с помощью шаблонов XSLT.

В заключение приведу координаты книги, которая очень помогла в освоении данного материала.

1. Хендерсон Кен. Профессиональное руководство по SQL Server. Хранимые процедуры, XML, HTML. СПб.: Питер, 2005. ISBN 5-469-00046-X

Несколько ссылок на полезные материалы по SqlXml

2. http://www.rsdn.ru/article/db/xmlsql2.xml - статья в электронном журнале

3. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/ html/anch_SQLXML.asp - первичная информация по связке SQL Server и SqlXml.

4. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/ htm/dotnet_4wq4.asp - справочник по объектам SqlXml

Оригинал статьи расположен на сайте http://rsl.whatis.ru