XML+MSSQL+ASP.NET. Часть 1.
Автор: Измайлов Феликс (qstart@narod.ru)
Опубликовано: 22.01.2006
Источник: ASP.NET Mania
Перед написанием этой статьи, меня одолевали серьезные сомнения по двум причинам. Первое - это необъятность темы, второе - это аудитория, которой может быть полезна данная информация. Для профессионалов излагать этот материал смешно, а для "вновь прибывших" - слишком много надо объять необъятного в рамках нашего жанра. В одну статью сотни книг не поместишь.
Но, исходя из того, что наш сайт имеет направление на популяризацию тех или иных технологий (решений) и все материалы основываются на нашем собственном опыте, мы пришли к мнению, что надо изложить те результаты работы с 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
|
