Шаг 2: Создание хранимой процедуры
Чтобы извлечь наши данные в формате XML, мы сжимаем наш пакет запросов в одну хранимую процедуру. Это поощряет стратегии повторного использования кода и легко модифицируется в будущем. Начнем с загрузки анализатора запросов на наш сервер базы данных ( Start -> Programs -> Microsoft SQL Server -> Query Analyser ).
При появлении запроса введите учетные данные для входа в базу данных. Вы должны быть подключены к серверу, на котором вы создали базу данных myProducts. Затем введите следующий T-SQL, который я вскоре объясню:
use myProducts go CREATE PROCEDURE sp_GetExplicitXML AS SELECT 1 AS Tag, NULL AS Parent, c.catName as [Category!1!CatName], NULL as [Product!2!ProdName], NULL as [Product!2!Description] FROM categories c UNION ALL SELECT 2 AS Tag, 1 AS Parent, c.catName, p.productName, d.descText FROM categories c, products p, descriptions d WHERE c.catId = p.productCat AND p.productId = d.descProdId ORDER BY [Category!1!CatName], [Product!2!ProdName] FOR XML EXPLICIT
Не пугайтесь приведенного выше кода для хранимой процедуры, обещаю, это просто! Позвольте мне объяснить код шаг за шагом.
use myProducts go CREATE PROCEDURE sp_GetExplicitXML AS
Если вы когда-либо работали с T-SQL, вы должны быть знакомы с этими командами. Команда «использовать myProducts» сообщает SQL Server «выполнять любые запросы, которые мы выполняем к базе данных myProducts». Команда go указывает серверу SQL выполнить весь код, который находится над этой строкой, прямо сейчас. Далее мы сообщаем SQL-серверу, что создадим новую хранимую процедуру с именем sp_GetExplicitXML . Хранимая процедура не принимает входные параметры и не возвращает никаких выходных значений.
SELECT 1 AS Tag, NULL AS Parent, c.catName as [Category!1!CatName], NULL as [Product!2!ProdName], NULL as [Product!2!Description] FROM categories c UNION ALL SELECT 2 AS Tag, 1 AS Parent, c.catName, p.productName, d.descText FROM categories c, products p, descriptions d
Это основной кусок кода для нашей хранимой процедуры. Помните, как я говорил ранее, что при использовании режима «FOR XML EXPLICIT» вы можете контролировать форму, имена столбцов и содержимое возвращаемого XML? Ну, это код, который делает это для нас. Код выше действует как шаблон, в котором будет создана универсальная таблица. Универсальная таблица похожа на обычно отображаемую таблицу SQL с несколькими отличиями:
- Универсальная таблица имитирует иерархическую структуру, используя поля «тег» и «родитель» для измерения глубины иерархии данных. В приведенном выше коде вы заметите, что первая команда select имеет тег 1 без родительского элемента. В следующем операторе выбора есть тег 2 с родителем 1, что означает, что все результаты, возвращаемые из второго оператора выбора, будут дочерними по отношению к первому оператору выбора.
- Как вы, наверное, заметили, имена столбцов для универсальной таблицы отличаются от обычных имен столбцов. Давайте разберем имя одного из имен наших универсальных столбцов таблицы из приведенного выше кода:
-
[Category!1!CatName]Как видите, имя столбца имеет три значения, разделенных восклицательным знаком. Первое значение — это имя элемента XML, который будет создан в универсальной таблице. Второй — индекс тега, который позволяет внутреннему XML-указателю SQL знать, на каком уровне иерархии будут размещаться эти данные. Последнее значение, «CatName» — это имя атрибута, в которое будет вставлено значение оператора select. Пример элемента категории из нашей базы данных будет выглядеть так:<Category CatName = «ASP»>
Обратите внимание, что в первом операторе select есть два значения NULL? Это связано с тем, что в первом утверждении нас интересуют только категории для наших книг, которые взяты из таблицы категорий (« FROM categories c »). Эти значения остаются пустыми, потому что они будут заполнены в следующем операторе выбора (где мы имеем дело с таблицами категорий, продуктов и описаний). Если все это немного сбивает с толку, взгляните на диаграмму ниже:
Теперь к последней части кода.
WHERE c.catId = p.productCat AND p.productId = d.descProdId ORDER BY [Category!1!CatName], [Product!2!ProdName] FOR XML EXPLICIT
В этом последнем фрагменте нашего кода мы проследим, чтобы в каждой категории отображались только те товары, у которых поле productCat равно полю catId . Кроме того, мы сопоставляем каждый продукт с его описанием с помощью p.productId = d.descProdId « p.productId = d.descProdId ». Наконец, мы сортируем вывод XML по возрастанию имени категории и по возрастанию имени продукта (примечание: не забудьте запустить код, нажав Alt + X).