DROP DATABASE TestDB1

DROP DATABASE имя_БД

ALTER DATABASE имя_БД

Пример.

EXEC sp_helpdb TestDB1

Изменение параметров БД (ALTER DATABASE)

Выполняется с помощью команды

MODIFY FILE логическое_имя (SET параметр = значение)

Пример. Установление максимального размера файла данных

ALTER DATABASE TestDB1

MODIFY FILE TestDB1_Data (SET MAXSIZE = 5)

Удаление БД (DROP DATABASE)

Выполняется с помощью команды

Пример. Удаление БД TestDB1

Создание Групп файлов (Filegroups)

Файловая группа – логическая совокупность файлов данных, которая позволяет администратору управлять файлами внутри файловой группы как одним файлом. Например, вы можете использовать файловые группы для контроля физического расположения объектов БД и для разделения изменяемых данных от данных, предназначенных только для чтения.

Типы файловых групп. База данных содержит файловую группу Primary (первичная) и может содержать несколько групп, определенных пользователем.

· Файловая группа Primary содержит первичный файл данных с системными таблицами. Первичный файл данных обычно имеет расширение .mdf.

· Файловая группа, определенная пользователем состоит из файлов данных, сгруппированных вместе для одних и тех же административных задач. Эти файлы данных принято называть вторичными, и они обычно имеют расширение .ndf.

Пример сценария

Можно создать файловые группы для разделения часто запрашиваемых файлов данных от часто изменяемых файлов данных. Например, файлы OrdHist1.ndf и OrdHist2.ndf расположены отдельно от таблиц Product, Customer и SalesOrderHeader. Нельзя располагать журнал транзакций в файловых группах, т.к. журнал транзакций управляется отдельно от пространства данных. Журнал транзакций имеет обычно расширение .ldf. Пример создания БД:

CREATE DATABASE [AdventureWorks] ON

PRIMARY

( NAME = N'AdventureWorks_Data', FILENAME = N'C:\AdventureWorks_Data.mdf' ),

FILEGROUP [OrderHistoryGroup]

( NAME = N'OrdHist1', FILENAME = N'D:\OrdHist1.ndf' ),

( NAME = N'OrdHist2', FILENAME = N'D:\OrdHist2.ndf' )

LOG ON

( NAME = N'AdventureWorks_log', FILENAME = N'E:\AdventureWorks_log.ldf')

Для удаления или добавления файлов и файловых групп существующей БД можно использовать команду ALTER DATABASE. Например:

ALTER DATABASE TestDB

ADD FILEGROUP [Secondary]

GO

ALTER DATABASE TestDB

ADD FILE (NAME=N’Test2’,

FILENAME=N’C:\Program Files\Microsoft SQL Server\

MSSQL.1\MSSQL\Data\Test2.ndf’)

TO FILEGROUP [Secondary]

GO

ALTER DATABASE TestDB MODIFY FILEGROUP [Secondary] DEFAULT

GO

Используя файловые группы, вы можете:

· Хранить изменяемые данные отдельно от данных, предназначенных только для чтения.

· Хранить индексы отдельно от таблиц.

· Резервировать и восстанавливать некоторые файлы или файловые группы вместо целой БД.

· Группировать таблицы и индексы с похожими требованиями сопровождения в одной и той же файловой группе.

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

· Хранить секции секционированных таблиц в разных файловых группах. Это хороший способ физически разделить данные для различных нужд доступа внутри одной таблицы и может дать выигрыш в производительности.

Создание Схем (Schemas)

Объекты базы данных, такие как таблицы, индексы, представления, хранимые процедуры всегда создаются внутри некоторой схемы.

Схема – это пространство имен объектов базы данных. Внутри схемы все имена объектов уникальны. Каждый объект базы данных имеет полную спецификацию из четырех частей:

сервер.база данных.схема.объект

Внутри базы данных спецификацию можно сократить:

схема.объект

Пример.

Преимущества:

· Упрощается управление правами, т.к. право может быть дано на схему, а не каждый объект БД в отдельности.

· Удаление пользователя не ведет к переименованию всех объектов, которыми он владел.

В базе данных AdwentureWorks используются следующие схемы:

· HumanResources

· Person

· Production

· Purchaising

· Sales

Например, к таблице Employee из схемы HumanResources можно обратиться как HumanResources.Employee.

Каждая база данных содержит схему dbo. Схема dbo является схемой по умолчанию для всех пользователей, которые не имеют своей схемы, определенной для них по умолчанию.

Чтобы создать схему, можно использовать Object Explorer в SQL Server Management Studio или оператор CREATE SCHEMA

Use AdventureWorks

GO

CREATE SCHEMA Sales

GO

Когда БД содержит множество схем, автоматическое определение имени объекта может быть неожиданным для пользователя. Например, база данных может содержать две таблицы Order в разных схемах, например в Sales и dbo. Тогда к каждой из этих таблиц рекомендуется обращаться как Sales.Order и dbo.Order соответственно. Иначе, если обратиться только по имени, результат может быть неожиданным – запрос пользователя может быть обращен не к той таблице, к которой он ожидал обратиться. Можно назначить пользователю схему по умолчанию.

Используется следующий процесс определения неквалифицированного имени объекта:

1. Если пользователь имеет схему по умолчанию, SQL Server пытается найти объект в схеме по умолчанию.

2. Если объект не найден в схеме по умолчанию, или пользователь не имеет схемы по умолчанию, то SQL Server пытается найти объект в схеме dbo.

Например, пользователь со схемой Person выполняет следующий запрос:

SELECT * FROM Contact

Сначала таблица Contact ищется в схеме Person. Если в схеме Person нет таблицы Contact, то таблица ищется в схеме dbo.

Если пользователь без схемы по умолчанию выполняет тот же запрос, то SQL Server сразу же ищет объект dbo.Contact.

Вы можете назначить схему по умолчанию, используя диалоговое окно свойств пользователя БД или с помощью фразы DEFAULT_SCHEMA операторов CREATE USER или ALTER USER языка Transact-SQL. Например,

ALTER USER Anders WITH DEFAULT_SCHEMA = Sales

Создание Снапшотов (Snapshots)

Существует множество сценариев, в которых полезна простая копия базы данных – снапшот. Например, для тестирования и разработки БД или просто для создания отчета. Вы можете использовать фразу AS SNAPSHOT OF оператора CREATE DATABASE, чтобы создать снапшот.

Снапшот БД – это предназначенное только для чтения, статическое представление БД в определенный момент времени.

Снапшот БД может быть полезен при повреждении данных в базе. Однако снапшоты не могут быть использованы для восстановления БД, так как содержат не все записи БД.

Существуют ограничения на использование снапшотов.

1. Снапшоты не могут быть созданы для системных баз данных model, master, tempdb.

2. Снапшоты не могут резервироваться или восстанавливаться.

3. Снапшоты не могут присоединяться или отсоединяться.

4. Все снапшоты БД должны быть удалены перед удалением БД

5. SQL Server Management Studio не поддерживает создание снапшотов, они создаются только командами Transact-SQL.

Снапшоты БД поддерживают статическое представление БД с помощью копирования данных перед их модификацией.

SQL Server использует технологию copy-on-write для выполнения снапшотов БД, при которой не требуется хранить полную копию базы данных. После создания снапшот вначале пустой. Когда страница базы данных-источника модифицируется, то исходное состояние этой страницы копируется в снапшот. Если страница никогда не изменялась, то она не хранится в снапшоте.

Копирование в снапшот выполняется на уровне страниц, даже если изменилась всего одна строка. Использование страниц вместо строк более эффективно. Страница содержит множество строк, если изменяются несколько строк одной страницы, то производится всего одна операция копирования. Такой механизм эффективен даже для часто изменяющихся баз данных.

Пользователь, обращающийся к снапшоту БД, увидит копию страницы в снапшоте, если эта страница была модифицирована после создания этого снапшота. Иначе, пользователь перенаправляется к соответствующей странице в базе-источнике. Это перенаправление выполняется автоматически и невидимо (прозрачно) для пользователя.

Пример.

Создается снапшот базы данных AdventureWorks:

CREATE DATABASE AdventureWorks_Snapshot1200 ON
( NAME = N’AdventureWorks_Data’, FILENAME=
N’C:\ Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AW1200.ss’)
AS SNAPSHOT OF AdventureWorks

После создания снапшота обе следующие команды дадут идентичный результат:

SELECT * FROM AdventureWorks.Person.Address WHERE AddressID=1

SELECT * FROM AdventureWorks_Snapshot1200.Person.Address WHERE AddressID=1

Изменим строку:

UPDATE AdventureWorks SET Address=’1000 Napa Ct.’ WHERE AddressID=1

Если теперь выполнить предыдущие команды SELECT, то они вернут разный результат. В первом случае будет обновленная строка, а во втором – строка до обновления.

Лекция 2. Создание типов данных и таблиц

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

Создание типов данных (Types)

Типы данных определяют набор возможных значений для каждого столбца таблицы БД. SQL Server поддерживает некоторое количество системных типов данных. Необходимо использовать по возможности самые короткие типы данных. Это позволит поместиться большему количеству строк на странице, что обеспечит наилучшую производительность.

В следующей таблице приведены некоторые системные типы данных SQL Server.

Можно определить собственный тип данных на основе одного из существующих системных типов. Пользовательский тип данных:

· Определяется для конкретной базы данных

· Должен иметь уникальное имя внутри БД

Пользовательский тип данных следует создавать, если необходимо определить элементы данных в различных таблицах с одним и тем же форматом. Например, столбец с кодом страны в соответствии со стандартом ISO – 2 латинских символа: JP – Япония, CH – Швеция, RU – Россия и т.д. Различные таблицы могут содержать столбец, хранящий код страны. Тогда можно определить тип данных CountryCode и использовать его в разных таблицах в качестве типа данных.

Если создать тип данных CountryCode в БД model, то он автоматически будет включаться во все вновь создаваемые БД.

Чтобы создать тип данных, необходимо использовать Object Explorer в SQL Server Management Studio или команду CREATE TYPE языка Transact-SQL. Пример:

CREATE TYPE dbo.CountryCode

FROM char(2)

NULL

Чтобы изменить созданный ранее тип данных используется команда ALTER TYPE. Чтобы удалить – команда DROP TYPE.

Создание таблиц

После определения типов данных, если это необходимо, можно приступить к созданию таблиц. Необходимо понимание того, как SQL Server хранит данные, и как определяются столбцы для оптимального хранения и производительности.

Как организовано хранение данных в строках

Строка данных состоит из заголовка и данных.

Четырехбайтный заголовок строки содержит информацию о столбцах: указатель конца порции данных фиксированной длины и существуют ли данные переменной длины в данной строке.

Раздел данных строки может содержать следующие элементы:

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

Null-блок. Содержит набор байт переменной длины. Он состоит из 2 байт, хранящих число столбцов, имеющих null-значения. Далее следует битовая карта, показывающая, какие столбцы принимают null-значения. Размер битовой карты равен числу бит из расчета 1 бит на столбец, округленных до ближайшего большего числа байт. Восемь столбцов требуют 1-байтной битовой карты, 16 столбцов – 2-хбайтной и т.д.

Переменный блок. Первые 2 байта хранят число столбцов переменной длины. Дополнительно, каждые следующие 2 байта хранят указатель на конец столбца переменной длины. Переменный блок отсутствует, если нет столбцов переменной длины.

Данные переменной длины. Эти данные следуют за данными фиксированной длины. Пустые данные переменной длины не занимают места. Поэтому таблица со столбцами переменной длины может иметь строки разной длины.

Как хранятся большие значения данных

Строка не может быть больше чем страница данных. Поэтому, столбец не может хранить значение, занимающее более чем 8 Кб. Большие данные хранятся отдельно от строки, а в строке содержится только указатель на эти данные.

Типы данных LOB (Large Object) могут храниться как одна коллекция страниц или в строках данных. К типам данных LOB относятся:

text. Этот тип данных может хранить до 2 147 483 647 символов. Этот тип данных не может использоваться в хранимых процедурах.

ntext. Может хранить в 2 раза меньше символов, чем тип данных text.

image. Может хранить до 2 Gb двоичных данных.

Так как text, ntext и image хранят обычно большие объемы данных, то SQL Server хранит их отдельно от строк. 16-байтный указатель в строке данных указывает на корень B-дерева, содержащего данные. Этот корень есть указатель на блоки данных. Если данные занимают больше, чем 32 Kb, то организуются промежуточные узлы между корнем и блоками данных.

Если содержимое LOB типов не очень большое (до 8 Kb), то SQL Server допускает хранение их значений непосредственно в строках данных.

С типами данных varchar, nvarchar и varbinary может использоваться спецификатор max. Тогда они допускают значения более 8 Kb и эти значения хранятся подобно значениям LOB-типов.

Можно управлять местом хранения больших значений типов varchar, nvarchar и varbinary с помощью опции large value types out of row хранимой процедуры sp_tableoption. Если эта опция имеет значение ON, то большие данные хранятся отдельно от строк. Иначе (опция OFF), эти данные могут храниться непосредственно в строке или отдельно в зависимости от того, сколько они реально занимают места.

Рекомендации по созданию таблиц

Когда вы создаете таблицу, обязательно нужно указать имя таблицы, имена столбцов и их типы данных. Имена столбцов должны быть уникальны в пределах одной таблицы. Ограничения:

· В БД может быть более 2 млрд объектов, в том числе таблиц.

· В таблице может быть до 1024 столбца

· Строка может занимать не более 8 Kb (это не относится к типам LOB и типам данных со спецификатором max)

Для каждого столбца можно указать, может ли он принимать Null-значения. Если этого не указать, то умолчание определяется опцией ANSI_NULL_DEFAULT на уровне базы данных или сессии. Если эта опция ON, то по умолчанию считается NULL. Иначе – NOT NULL. В SQL Server 2005 опция ANSI_NULL_DEFAULT равна OFF. Не следует полагаться на умолчания, а следует всегда явно указывать, может ли столбец принимать NULL-значения.

Существуют специальные типы столбцов:

· Вычисляемые (computed columns). Это виртуальные столбцы, которые физически не хранятся в таблице. Их значения вычисляются по указанной формуле на основе значений других столбцов этой же таблицы. Использование вычисляемых столбцов может упростить синтаксис запроса.

· Счетчики (identity columns). Можно использовать свойство Identity, чтобы создать столбец, который содержит последовательно сгенерированные значения, идентифицирующие каждую строку, вставляемую в таблицу. Свойство identity обычно используется для первичных ключей таблиц.

· Столбцы с типом timestamp. В столбцы с типом данных timestamp автоматически вставляется текущая дата и время.

· Столбцы с типом uniqueidentifier. Эти столбцы используются для хранения глобального уникального идентификатора с помощью функции NEWID языка Transact-SQL.

Пример создания таблицы:

CREATE TABLE Sales.CustomerOrders

(OrderID int identity NOT NULL,

OrdderDate datetime NOT NULL,

CustomerID NOT NULL,

Notes nvarchar(200) NULL)

Можно также создать таблицу с помощью Object Explorer в SQL Server Management Studio.

Изменение и удаление таблиц

Можно изменить или удалить таблицу, используя команды Transact SQL или Object Explorer в SQL Server Management Studio. Пример изменения таблицы:

ALTER TABLE Sales.CustomerOrders

ADD SalesPersonID int NOT NULL

GO

ALTER TABLE Sales.CustomerOrders

ALTER Notes nvarchar(200) NOT NULL

GO

Для удаления таблицы используется команда DROP TABLE.

Создание секционированных таблиц

Секционированная таблица – таблица, данные которой физически разделены горизонтально на основе ранжирования значений определенного столбца. Физическое расположение для секций определено в файловых группах. Например, можно разделить информацию таблицы заказов по дате: заказы до 2003 года хранить в одной секции, с 2003 по 2004 – в другой секции, 2004-2005 – в третьей и т.д. Эта техника делает возможным управлять физическим расположением строк одной таблицы.

Преимущества в управлении:

· Возможность выполнять различные стратегии резервирования. Различные наборы данных могут иметь различные требования к резервированию. Например, недавние данные о заказах могут часто обновляться и требовать регулярного резервирования., в то время как старые заказы изменяются крайне редко и требуют более редкого резервирования.

· Возможность управлять хранением. Секционирование таблиц позволяет выбрать подходящее хранилище для данных. Например, исторические неизменяемые данные можно хранить в сжатых файловых группах, в то время как текущие данные – на высокопроизводительных файловых группах в RAID.

· Лучшее управление индексами. Дополнительно к секционированию таблиц, можно секционировать индексы. Это позволяет реорганизовывать, оптимизировать и перестраивать индексы по секциям, что выполнится гораздо быстрее, чем для целого индекса. Кроме того, секционирование индекса может уменьшить фрагментацию. Например, старые данные о заказах меньше изменяются и соответствующие индексные страницы стабильны.

Преимущества в производительности:

· Более быстрый индексный поиск. Секционирование результатов в малых индексных деревьях для каждой секции выполняется быстро, особенно когда количество строк ограничено фразой WHERE.

· Быстрее выполняются операции JOIN, когда соединяются таблицы, секционированные по одному и тому же принципу.

· Уменьшается количество блокировок на уровне секции, значительно уменьшая риск взаимоблокировок.

Функции секционирования (Partition Functions)

Функции секционирования определяют тип данных ключа секционирования и граничные значения для каждой секции. Число секций зависит от функции секционирования и всегда на единицу больше, чем число граничных значений, определенных в функции.

Например, функция секционирования с ключом типа datetime и граничными значениями ‘01/01/2003’, ‘01/01/2004’, ‘01/01/2005’ определит 4 секции по периодам: до 01.01.2003, 01.01.2003-31.12.2003, 01.01.2004-31.12.2004, после 01.01.2005.

Функции секционирования можно сконфигурировать как левые или правые (LEFT, RIGHT). В функции LEFT граничное значение входит в левую секцию. В функции RIGHT граничное значение входит в правую секцию.

Функцию секционирования можно создать с помощью оператора CREATE PARTITION FUNCTION языка Transact-SQL. Пример:

CREATE PARTITION FUNCTION pf_orderDate (datetime)

AS RANGE RIGHT

FOR VALUES (‘01/01/2003’, ‘01/01/2004’, ‘01/01/2005’)

Схема секционирования (Partition Scheme)

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

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

Схему секционирования можно создать с помощью оператора CREATE PERTITION SCHEME языка Transact-SQL:

CREATE PARTITION SCHEME ps_OrderDate

AS PARTITION pf_OrderDate

TO (fg1,fg2,fg3,fg4,fg5)

Если вы хотите поместить все секции в одну файловую группу, то используйте ключевое слово ALL, например:

CREATE PARTITION SCHEME ps_ordderDate

AS PARTITION pf_OrderDate

ALL TO ([PRIMARY])

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

Операции над секционированными таблицами

Кроме преимуществ, рассмотренных выше, секционированные таблицы могут обеспечить очень мощные возможности. Можно выполнить три операции над секциями:

· Переключение (SWITCH)

· Слияние (MERGE)

· Разделение (SPLIT)

Можно обменять заполненную таблицу или секцию с пустой таблицей или секцией при использовании фразы SWITCH оператора ALTER TABLE. Эта техника обычно используется для архивирования данных секционированной таблицы или для массовой загрузки (bulk insert) новых данных из некоторой таблицы.

Следующий код показывает, как переключить секцию:

ALTER TABLE dbo.PartitionedTr

SWITCH PARTITION 1

TO dbo.ArchivedTr

Можно слить две секции в одну, использую оператор ALTER PARTITION FUNCTION.

Когда выполняется операция слияния, удаляется секция, в которую входит граничное значение, указанное в операторе ALTER PARTITION FUNCTION, а данные записываются в секцию, смежную с удаленной. Пример:

ALTER PARTITION FUNCTION pf_OrderDate()

MERGE RANGE (‘01/01/2003’)

Подобно слиянию, операция разделения выполняется при помощи оператора ALTER PARTITION FUNCTION. При этом создается новая секция и данные соответствующим образом разделяются по двум секциям. Новая секция создается в файловой группе next. Если такой группы нет, то возникает ошибка.

Пример:

ALTER PARTITION FUNCTION pf_OrderDate()

SPLIT RANGE (‘01/01/2006’)

Лекция 3
Получение XML-документов из реляционных таблиц (FOR XML)

Введение

Фраза FOR XML является центральной для поиска данных XML в SQL Server 2005. Эта фраза является инструкцией для SQL Server, чтобы возвратить данные в формате XML, а не в виде набора строк. Разработчики приложений могут построить решения, которые выполняют извлечение бизнес XML документы, таких как заказы, счета, или каталоги непосредственно из базы данных.

В этом уроке описывается, как использовать фразу FOR XML и ее различные опции, чтобы сформировать данные в формате XML.

Введение в FOR XML

Введение

Вы можете использовать фразу FOR XML в операторе SELECT языка Transact-SQL, чтобы получить данные в формате XML вместо строк и столбцов. Вы можете управлять форматом XML, определяя один из четырех режимов: RAW, AUTO, EXPLICIT или PATH . Кроме того, Вы можете определить различные опции для управления выводом.

Синтаксис фразы FOR XML

Фраза FOR XML прилагается к оператору SELECT, в соответствии со следующим синтаксисом.

FOR XML

{ { RAW [ ( 'ElementName' ) ] | AUTO }

[ <CommonDirectives>

[ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ]

[ , ELEMENTS [ XSINIL | ABSENT ] ]

| EXPLICIT

[ <CommonDirectives> [ , XMLDATA ] ]

| PATH [ ( 'ElementName' ) ] [<CommonDirectives>

[ , ELEMENTS [ XSINIL | ABSENT ] ] ] }

<CommonDirectives> ::=

[ , BINARY BASE64 ] [ , TYPE ] [ , ROOT [ ( 'RootName' ) ] ]

Обычно используемые режимы и опции фразы FOR XML описываются в следующей таблице:

Примеры использования FOR XML

Существует множество ситуаций, в которых Вы хотели бы получить данные в формате XML вместо набора строк. Например, рассмотрите следующие сценарии доступа к данным:

■ Получение данных для публикации на Web-сайте,

Получая данные в формате XML, Вы можете применить стилевые таблицы XSLT для передачи данных в HTML-формате. Вы можете также применить различные стилевые таблицы к тем же самым данным XML, чтобы сгенерировать альтернативные форматы представления, чтобы поддержать различные устройства клиента, не переписывая логики доступа к данным.

■ Получение данных для обмена с торговым партнером

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

Запросы в режиме RAW

Введение

Вы используете запросы в режиме RAW, чтобы получить XML представление набора строк. Приложения могут обработать XML в естественном формате или применить стилевые таблицы XSLT, чтобы преобразовать XML в необходимый деловой формат документа или представление в интерфейсе пользователя.

Рассмотрите следующие особенности режима RAW:

■ элемент представляет каждую строку в результирующем наборе, который возвращает запрос.

■ атрибут с именем столбца или псевдонимом, используемым в запросе, представляет каждый столбец в результирующем наборе, если не указана опция ELEMENTS, когда каждый столбец соответствует подэлементу элемента строки.

■ запросы в режиме RAW могут включать агрегированные столбцы и фразу GROUP BY.

Получение данных в сгенерированных элементах-строках

В следующем примере показывается, как Вы можете получить XML-фрагмент, содержащий данные заказа при использовании фразы FOR XML в режиме RAW.

SELECT Cust.CustomerID CustID, CustomerType, SalesOrderID

FROM Sales.Customer Cust JOIN Sales.SalesOrderHeader [Order]

ON Cust.CustomerID = [Order].CustomerID

ORDER BY Cust.CustomerID

FOR XML RAW

Этот запрос выводит XML-фрагмент в формате, который содержит сгенерированные элементы <raw>, показанные в следующем примере.

<row CustID="1" CustomerType="S" SalesOrderID="43860"/>

<row CustID="1" CustomerType="S" SalesOrderID="44501"/>

Заметьте, что столбец CustomerIDиспользует псевдоним CustID, который определяет имя атрибута.

Получение данных в виде элементов

В следующем примере показывается, как Вы можете получить те же самые данные в виде элементов вместо атрибутов, определяя опцию ELEMENTS.

SELECT Cust.CustomerID CustID, CustomerType, SalesOrderID

FROM Sales.Customer Cust JOIN Sales.SalesOrderHeader [Order]

ON Cust.CustomerID = [Order].CustomerID

ORDER BY Cust.CustomerID

FOR XML RAW, ELEMENTS

Этот запрос выводит XML-фрагмент в формате, показанном в следующем примере.

<row>

<CustID>1</CustID>

<CustomerType>S</CustomerType>

<SalesOrderID>43860</SalesOrderID>

</row>

<row>

<CustID>1</CustID>

<CustomerType>S</CustomerType>

<SalesOrderID>44501</SalesOrderID>

</row>

Получение данных при использовании корневого элемента и настроенного имени элемента строки

В следующем примере показывается, как Вы можете получить те же самые данные, используя корневой элемент, определенный с помощью опции ROOT и изменяя имя элемента строки при помощи необязательного аргумента опции RAW.

SELECT Cust.CustomerID CustID, CustomerType, SalesOrderID

FROM Sales.Customer Cust JOIN Sales.SalesOrderHeader [Order]

ON Cust.CustomerID = [Order].CustomerID

ORDER BY Cust.CustomerID

FOR XML RAW('Order'), ROOT('Orders')

Этот запрос предоставляет правильно построенный XML документ в следующем формате:

<Orders>

<Order CustID="1" CustomerType="S" SalesOrderID="43860"/>

<Order CustID="1" CustomerType="S" SalesOrderID="44501"/>

</Orders>

Заметьте, что, если бы была также определена опция ELEMENTS, то получающиеся строки содержали бы элементы вместо атрибутов.

Запросы в режиме AUTO

Введение

Запросы в режиме AUTO производят XML представления данных о сущностях. Рассмотрите следующие особенности опции AUTO:

■ Каждая строка, возвращаемая в соответствии с запросом, представляется элементом XML с тем же самым именем как у таблицы, из которой она была получена (или с псевдонимом, используемым в запросе).

■ Каждое соединение JOIN в запросе преобразуется во вложенный элемент XML, уменьшая дублирование данных в получающемся фрагменте XML. Порядок операторов JOIN влияет на порядок вложенных элементов.

■ Чтобы гарантировать, что дочерние элементы сопоставлены правильно с их родителем, используйте фразу ORDER BY, чтобы возвратить данные в правильном иерархическом порядке.

■ Каждый столбец в результирующем наборе представлен атрибутом, пока не определена опция ELEMENTS, когда каждый столбец представлен дочерним элементом.

■ Агрегированные столбцы и фраза GROUP BY не поддерживается в запросах в режиме AUTO (хотя Вы можете использовать запросы в режиме AUTO, чтобы получить агрегированные данные из представления, которое использует фразу GROUP BY).

Получение вложенных данных при использовании режима AUTO

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

SELECT Cust.CustomerID CustID, CustomerType, SalesOrderID

FROM Sales.Customer Cust JOIN Sales.SalesOrderHeader [Order]

ON Cust.CustomerID = [Order].CustomerID

ORDER BY Cust.CustomerID

FOR XML AUTO

Этот запрос производит фрагмент XML в формате, показанном в следующем примере.

<Cust CustID="1" CustomerType="S">

<Order SalesOrderID="43860"/>

<Order SalesOrderID="44501"/>

</Cust>

Заметьте, что столбец CustomerIDи таблицы Customerи SalesOrderHeaderиспользуют псевдонимы для определения имен атрибута и элементов.

Получение данных в виде элементов

Следующий пример показывает, как Вы можете получить те же самые данные в виде элементов а не в виде атрибутов, определяя опцию ELEMENTS.

SELECT Cust.CustomerID CustID, CustomerType, SalesOrderID

FROM Sales.Customer Cust JOIN Sales.SalesOrderHeader [Order]

ON Cust.CustomerID = [Order].CustomerID

ORDER BY Cust.CustomerID

FOR XML AUTO, ELEMENTS

Этот запрос производит фрагмент XML в формате, показанном в следующем примере.

<Cust>

<CustID>1</CustID>

<CustomerType>S</CustomerType>

<Order>

<SalesOrderID>43860</SalesOrderID>

</Order>

<Order>

<SalesOrderID>44501</SalesOrderID>

</Order>

</Cust>

...

Так же, как и в режиме RAW, Вы можете использовать опцию ROOT, как показано в следующем примере.

SELECT Cust.CustomerID CustID, CustomerType, SalesOrderID

FROM Sales.Customer Cust JOIN Sales.SalesOrderHeader [Order]

ON Cust.CustomerID = [Order].CustomerID

ORDER BY Cust.CustomerID

FOR XML AUTO, ELEMENTS, ROOT('Orders')

Этот запрос представляет документ XML в формате, показанном в следующем примере.

<Orders>

<Cust>

<CustID>1</CustID>

<CustomerType>S</CustomerType>

<Order>

<SalesOrderID>43860</SalesOrderID>

</Order>

<Order>

<SalesOrderID>44501</SalesOrderID>

</Order>

</Cust>

...

</Orders>

Запросы в режиме EXPLICIT

Введение

Иногда деловые документы, которые Вы должны обменяться с торговыми партнерами, требуют формат XML, который не может быть получен при использовании режимов RAW или AUTO. Когда данные таблицы соответствуют элементу XML, столбцы таблицы могут быть представлены как

■ значение элемента.

■ атрибут.

■ дочерний элемент.

Универсальные таблицы

Ключом к пониманию формирования настраиваемых XML документов является понятие универсальной таблицы. Универсальная таблица - табличное представление XML документа. Каждая строка в универсальной таблице представляет данные, которые будут представлены в виде элемента в получающемся документе XML.

Первые два столбца универсальной таблицы определяют в результирующем XML документе иерархическое положение элемента, который содержит данные строки. Эти столбцы:

Tag (Тег). Числовое значение, которое уникально идентифицирует тег для элемента, который содержит данные в этой строке.

Parent (Родитель). Числовое значение, которое идентифицирует непосредственный родительский тег для этого элемента.

Каждый различный XML тег в получающемся документе, который соответствует таблице или представлению в базе данных должен быть представлен различным значением Tagв универсальной таблице. Значение Parentопределяет иерархическое положение тега в получающемся документе. Теги верхнего уровня фрагмента XML (и поэтому не имеющие непосредственного родительского элемента) имеют значение Parent, равное NULL. Например, документ XML, описанный ранее, содержит элемент Invoice (счет)без родителя и элемент LineItem, который является дочерним элементом элемента Invoice. В универсальной таблице теги для этих двух элементов назначены в столбце Tag, чтобы идентифицировать их, а столбец Parentиспользуется, чтобы определить, как вложены элементы.

Определение столбцов универсальной таблицы

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

ElementName! TagNumber! AttributeName! Directive

Следующая таблица описывает части названия столбца.

Например, следующая универсальная таблица определяет информацию о счетах в XML формате.

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

<Invoice InvoiceNo="43659">

<Date>2001-07-01T00:00:00</Date>

</Invoice>

<Invoice InvoiceNo="43660">

<Date>2001-07-01T00:00:00</Date>

</Invoice>

Создание запроса для построения универсальной таблицы

После того, как Вы определили универсальную таблицу, необходимую для получения желаемого XML-документа, Вы можете построить запрос Transact-SQL, необходимый, чтобы сгенерировать таблицу с использованием псевдонимов для именования столбцов. Вы можете назначить значения для столбцов Tagи Parentявно, как показано в следующем примере.

SELECT 1 AS Tag,

NULL AS Parent,

Sales.SalesOrderID AS [Invoice!1!InvoiceNo],

OrderDate AS [Invoice!1!Date!Element]

FROM SalesOrderHeader

FOR XML EXPLICIT

Этот пример создает XML, показанный ранее в этой теме, комбинируя атрибуты и элементы для столбцов SalesOrderIDдля OrderDate.