Отправляет email-рассылки с помощью сервиса Sendsay

MS SQL Server

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "Вопросы и ответы по MS SQL Server" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Июнь 2002   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Автор
Сергей

Статистика

17.597 подписчиков
-20 за неделю
←   Все выпуски   →

MS SQL Server - дело тонкое...


Информационный Канал Subscribe.Ru
#100<<  #101

СОДЕРЖАНИЕ

1.СОВЕТЫ
1.1.XML в MS SQL Server 2000 и технологиях доступа к данным (продолжение)
1.1.1.Аннотированные схемы
1.1.2.XML-шаблоны как разновидность хранимых процедур
2.ФОРУМ SQL.RU
2.1.Самые популярные темы недели
2.2.Вопросы остались без ответа

СОВЕТЫ

XML в MS SQL Server 2000 и технологиях доступа к данным (продолжение)
По материалам конференции "Корпоративные базы данных 2002"

Автор: Алексей Шуленин

1. Введение
2. Сохранить как XML
3. Получить как поток XML
4. XML на стороне сервера. FOR XML
5. SQLXML-классы в .NET
6. FOR XML на стороне клиента
7. XML-представление наборов данных в ADO .NET
8. Прямые XPath-запросы к объектам SQL Server

9. Аннотированные схемы

По своей структуре это обычные XSD-схемы, в которых допускаются специальные аннотации, задающие их привязку к сущностям реляционной структуры: таблицам, полям, первичным и внешним ключам, отношениям между таблицами и т.д., благодаря чему данные, хранящиеся на SQL Server, можно привести к желаемой XSD- структуре и запрашивать затем как XML-документ (XPath, XQuery). В VS .Net входит удобный редактор XSD-схем, позволяющий собирать их, натаскивая drag-n-drop'ом элементы, атрибуты, типы и т.д. из панели инструментов. Редактор имеет две панели: одна показывает традиционный XML-код схемы, а другая - ее реляционный эквивалент в виде таблиц и связей между ними. При переключении происходит автоматическая валидация схемы, доступная также из меню (Schema -> Validate). В нем есть цветовое выделение синтаксических конструкций, intelisense-подсказки и многие другие приятные вещи. Итак, с помощью этого замечательного редактора я создаю вид моего XML-документа, который будет содержать, допустим, информацию по клиентам и сделанных ими заказах. 


<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="XMLSchema1" xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:complexType name="Клиент">
  <xs:sequence>
   <xs:element name="Адрес" type="Адрес" />
   <xs:element name="Заказы" type="Заказы" />
  </xs:sequence>
  <xs:attribute name="Имя" type="xs:string" />
  <xs:attribute name="Должность" type="xs:string" />
  <xs:attribute name="Фирма" type="xs:string" />
 </xs:complexType>
 <xs:complexType name="Адрес">
  <xs:sequence>
   <xs:element name="Страна" type="xs:string" />
   <xs:element name="Город" type="xs:string" />
   <xs:element name="Улица_дом" type="xs:string" />
   <xs:element name="Индекс" type="xs:string" />
  </xs:sequence>
 </xs:complexType>
 <xs:complexType name="Заказы">
  <xs:sequence>
   <xs:element name="Заказ" type="Заказ" minOccurs="0"
   maxOccurs="unbounded" />
  </xs:sequence>
 </xs:complexType>
 <xs:complexType name="Заказ">
  <xs:sequence>
   <xs:element name="Дата" type="xs:date" />
   <xs:element name="Стоимость" type="xs:float" />
  </xs:sequence>
 </xs:complexType>
</xs:schema>

Рис.3

Теперь, чтобы по этой схеме представить данные из SQL Server, сопоставим их элементам и атрибутам при помощи аннотаций - см. рис.4 - для поддержки которых в схеме делается ссылка на соответствующее пространство имен (xmlns:ms="urn:schemas-microsoft-com:mapping-schema"). 


<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="SQLSchema1" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:ms="urn:schemas-microsoft-com:mapping-schema">
 <xs:annotation>
  <xs:appinfo>
   <ms:relationship name="CustOrds" parent="Customers" parent-key="CustomerID"
   child="Orders" child-key="CustomerID" />
  </xs:appinfo>
 </xs:annotation>
 <xs:complexType name="Клиент">
  <xs:sequence>
   <xs:element name="Адрес" type="Адрес" ms:is-constant="1" />
   <xs:element name="Заказы" type="Заказы" ms:is-constant="1" />
  </xs:sequence>
  <xs:attribute name="Имя" type="xs:string" ms:field="ContactName" />
  <xs:attribute name="Должность" type="xs:string" ms:field="ContactTitle" />
  <xs:attribute name="Фирма" type="xs:string" ms:field="CompanyName" />
 </xs:complexType>
 <xs:complexType name="Адрес">
  <xs:sequence>
   <xs:element name="Страна" type="xs:string" ms:field="Country" />
   <xs:element name="Город" type="xs:string" ms:field="City" />
   <xs:element name="Улица_дом" type="xs:string" ms:field="Address" />
   <xs:element name="Индекс" type="xs:string" ms:mapped="false" />
  </xs:sequence>
 </xs:complexType>
 <xs:complexType name="Заказы">
  <xs:sequence>
   <xs:element name="Заказ" type="Заказ" minOccurs="0"
   maxOccurs="unbounded"
   ms:relation="Orders" ms:relationship="CustOrds" />
  </xs:sequence>
 </xs:complexType>
 <xs:complexType name="Заказ">
  <xs:sequence>
   <xs:element name="Дата" type="xs:date" ms:field="OrderDate" />
   <xs:element name="Стоимость" type="xs:float" ms:field="Freight" />
  </xs:sequence>
  <xs:attribute name="Номер" type="xs:string" ms:field="OrderID" />
 </xs:complexType>
 <xs:element name="Клиент" type="Клиент" ms:relation="Customers" />
</xs:schema>

Рис.4

Аннотация sql:relation используется для отображения узла на таблицу. Она не поддерживается в тэгах определения типа, т.е. только в xs:element и xs:attribute, поэтому нам пришлось ввести в схему элемент составного типа "Клиент": <xs:element name="Клиент" type="Клиент" ms:relation="Customers" />. Вложенные элементы соответствуют записям дочерней таблицы, поэтому для них требуется еще задать ms:relationship. Отношения между таблицами в терминах родительской и дочерней таблиц (parent / child) и полей, по которым устанавливается связь (parent-key / child-key), определяются как атрибуты элемента <ms:relationship> в разделе определения аннотаций <xs:annotation>, <xs:appinfo>. Затем это отношение можно использовать, чтобы вложить дочерние записи внутрь родительского элемента <xs:element name="Заказ" type="Заказ"... ms:relation="Orders" ms:relationship="CustOrds"/>. Если вложенный элемент не соответствует никакой дочерней таблице, а несет чисто контейнерную функцию (как, например, Адрес), то он помечается атрибутом ms:is-constant="1": <xs:element name="Адрес" type="Адрес" ms:is-constant="1"/>. Аннотация ms:field привязывает XML-узел к полю таблицы. Она не требуется, когда название атрибута совпадает с названием поля. Непривязанные атрибуты также не допускаются. Если мы не планируем брать значение узла из БД, но в силу каких-либо причин не можем исключить его из схемы, его нужно пометить аннотацией ms:mapped="false": <xs:element name="Индекс" type="xs:string" ms:mapped="false" />. От is-constant она отличается тем, что узел вообще исключается из результирующего XML-документа.
Разберем еще несколько аннотаций на примере схемы, которая воссоздает по таблице parent-child дерево иерархии в виде XML: 


<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:ms="urn:schemas-microsoft-com:mapping-schema"
id="SQLSchema2">
 <xs:annotation>
  <xs:appinfo>
   <ms:relationship name="Начальник-Подчиненный"
   parent="Employees" parent-key="EmployeeID"
   child="Employees" child-key="ReportsTo" />
  </xs:appinfo>
 </xs:annotation>
 <xs:element name="Сотрудник" type="Тип_Сотрудник"
 ms:relation="Employees" ms:key-fields="EmployeeID"
 ms:limit-field="ReportsTo" />
 <xs:complexType name="Тип_Сотрудник">
  <xs:sequence>
   <xs:element name="Сотрудник" type="Тип_Сотрудник"
   ms:relation="Employees" ms:key-fields="EmployeeID"
   ms:relationship="Начальник-Подчиненный" ms:max-depth="3" />
  </xs:sequence>
  <xs:attribute name="ID_сотрудника" type="xs:ID"
  ms:field="EmployeeID" ms:hide="true" />
  <xs:attribute name="Имя" type="xs:string" ms:field="FirstName" />
  <xs:attribute name="Фамилия" type="xs:string"
  ms:field="LastName" />
 </xs:complexType>
</xs:schema>

Рис.5

Он взят из документации к SQLXML 3.0. Таблица Employees содержит записи по сотрудникам и связана сама с собой, т.е. в поле ReportsTo для каждого сотрудника указывается EmployeeID его начальника.
ms:max-depth задает максимальную глубину вложенности рекурсии при раскрытии отношения "родитель-потомок". В отличие от предыдущей ситуации, где количество уровней в иерархии определялось длиной максимальной ветки связанных таблиц, глубина дерева в случае parent-child таблицы зависит от ветки, по которой мы идем от корня, и априори неочевидна. Не обладая в текущей версии специальным оператором построения иерархии по такому типу связи, SQL Server разрешает ее в последовательность соединенных UNION'ами SELECT'ов, каждый из которых соответствует уровню иерархии. Поэтому их число (ms:max-depth) SQL Server должен знать заранее. Максимальное значение для него волевым образом установлено в 50.
Другая аннотация - ms:limit-field - позволяет провести ограничение (WHERE) по какому-либо полю еще на уровне схемы, т.е. до того, как дело дойдет до XPath. Обычно она употребляется в паре с ms:limit-value, которая задает значение критерия. В данном случае эта аннотация опущена, что означает, что по умолчанию берется значение NULL. Таким образом, верхним уровнем в данной иерархии будут самые-самые начальники (у которых начальников нет: ReportsTo = Null).
Почему атрибут ID_сотрудника аннотирован как ms:hide="true"? Он несет чисто служебную информацию и вряд ли понадобится в XML-результате. Но его не хочется выключать из схемы при помощи ms:mapped="false", потому что он действительно привязан к информации в БД, которая понадобится в дальнейшем. Например, он может фигурировать в критерии XPath-запроса: cmd.CommandText = "Сотрудник[@ID_сотрудника='4']" (Чтобы этот запрос возвратил сотрудника с EmployeeID = 4, нужно убрать фильтрацию в схеме - ms:limit-field). Наконец, еще одна аннотация, которая сейчас необязательна, но встретится нам через параграф - это ms:key-fields. Она задает значения полей, составляющих первичный ключ таблицы.
Полный список аннотаций, естественно, не ограничивается теми, которые встретились в этих двух простых примерах схем. Он достаточно обширен и позволяет строить довольно нетривиальные соответствия между XML-схемой и реляционным содержанием. Его можно найти в документации на SQLXML 3.0. 


static void Annotated_XPathQuery_SQLXML()
{
 ...
 cmd.CommandText = "Клиент[Адрес/Страна='Spain' or Адрес/Страна='France']";
 cmd.SchemaPath = "..\\Schemas\\SQLSchema1.xsd";
 cmd.CommandType = SqlXmlCommandType.XPath;
 cmd.RootTag = "Клиенты";
 XmlDocument xml = new XmlDocument();
 xml.Load(cmd.ExecuteStream());
 ...
}

Скрипт 10

В Скрипте 10, как и в предыдущем примере (Скрипт 9), на SQL Server посылается XPath-запрос, однако теперь данные рассматриваются через призму выбранной аннотированной схемы (указывается в свойстве SqlXmlCommand.SchemaPath) и трактуются в соответствии с задаваемой ею структурой. В данном случае запрос выбирает всех клиентов из Испании и Франции и сделанные ими заказы. Встроенной поддержкой XPath (а также XQuery) SQL Server в настоящее время не располагает, поэтому XPath по дороге превращается в то, что ему более понятно, а именно - в SQL-запрос. Если быть совсем точным, то в запрос типа FOR XML EXPLICIT. Ради любопытства можете открыть Profiler и посмотреть его для Скрипта 10 (здесь я его приводить не буду, потому что он занял бы еще как минимум страницу). Поддерживается подмножество стандартного синтаксиса XPath в части осей, функций и операторов. Отрадно, что каждым SQLXML веб-релизом это подмножество расширяется.

[Содержание]

10. XML-шаблоны как разновидность хранимых процедур

Шаблоны (templates) объединяют в себе квантово-волновой (SQL / XML) дуализм SQL Server. Козьма Прутков мог бы смело их уподобить хранимым процедурам, которые допускают как SQL-, так и XPath-запросы. Шаблон - это XML-файл, структура которого показана на рис.6. 


<?xml version="1.0" encoding="utf-8" ?>
<Солянка xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:header>
     <sql:param name="Колво"></sql:param>
     <sql:param name="Сумма">1000</sql:param>
  </sql:header>
  <sql:query client-side-xml="0">
    SELECT TOP 1 * FROM Employees FOR XML AUTO
  </sql:query>
  <sql:query client-side-xml="1">
    SELECT c.ContactName, COUNT(o.OrderDate) AS 'Кол-во'
 FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
 GROUP BY c.ContactName HAVING COUNT(o.OrderDate) >= @Колво
 FOR XML NESTED
  </sql:query>
  <sql:xpath-query mapping-schema="..\Schemas\SQLSchema1.xsd">
 Клиент[number(Заказы/Заказ/Стоимость)&gt;$Сумма]
  </sql:xpath-query>
  <sql:xpath-query mapping-schema="..\Schemas\SQLSchema2.xsd">
    Сотрудник
  </sql:xpath-query>
</Солянка>

Рис.6

Он состоит из частей трех основных типов. Каждая часть является опциональной. В <sql:query> перечисляются операторы SQL, в <sql:xpath-query> - запросы XPath, в <sql:header> описываются параметры для запросов (как SQL, так и XPath). Привязка описаний параметров в заголовке к параметрам в запросах осуществляется при помощи атрибута name ("Колво" - @Колво, "Сумма" - $Сумма). В одной секции <sql:query> может находиться несколько SQL-запросов, в отличие от XPath-запросов, каждый из которых должен заключаться в свою секцию <sql:xpath-query>. В данном примере SQL-запросы пришлось разнести по разным секциям, так как первый из них выполняется на сервере, а второй - на клиенте. Атрибут client-side-xml эквивалентен свойству ClientSideXml класса SqlXmlCommand, с которым мы сталкивались в п.6. В <sql:query> могут присутствовать не только select'ы, но и операторы объявления и модификации данных, а также вызовы хранимых процедур, что позволяет помещать в секцию не просто несколько разрозненных операторов, а последовательность, связанную логикой выполнения. Условие - весь select-вывод должен идти в виде XML. Допускаются опции AUTO и EXPLICIT. XPath-запросы выполняются относительно заданной в атрибуте mapping-schema аннотированной схемы. Для каждого XPath-запроса она может быть своя. Отметим, что некоторые (хоть все) из задействованных аннотированных схем могут располагаться непосредственно в файле шаблона, так что предыдущий шаблон можно записать как: 


<?xml version="1.0" encoding="utf-8" ?>
<Солянка xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <xs:chema xmlns:xs="http://www.w3.org/2001/XMLSchema"
  _    xmlns:ms="urn:schemas-microsoft-com:mapping-schema"
      id="SQLSchema2" sql:is-mapping-schema="1">
...Схема с рис.5...
  </xs:schema>
 ...<sql:header> и <sql:query>-секции с рис.6...
  <sql:xpath-query mapping-schema="..\Schemas\SQLSchema1.xsd">
 Клиент[number(Заказы/Заказ/Стоимость)&gt;$Сумма]
  </sql:xpath-query>
  <sql:xpath-query mapping-schema="#SQLSchema2">
    Сотрудник
  </sql:xpath-query>
</Солянка>

Рис.7

Таким образом, результат выполнения шаблона может состоять из фрагментов различных схем, соответствующих разным секциям, объединенных корневым элементом. Остается сохранить его в файле формата UTF-8 (Скрипт 11). 


static void Execute_TemplateFile_SQLXML()
{
 ...
 cmd.CommandText = "..\\Templates\\XMLTemplate1.xml";
 cmd.CommandType = SqlXmlCommandType.TemplateFile;
 SqlXmlParameter prm = cmd.CreateParameter();
 prm.Name = "@Колво"; prm.Value = 20;
 XmlDocument xml = new XmlDocument();
 xml.Load(cmd.ExecuteStream());
 ...
}

Скрипт 11

Вызывающее приложение передает шаблону только один параметр @Колво = 20 (показать клиентов и количество сделанных ими заказов, если оно превышает 20). Для другого запроса будет взято значение параметра по умолчанию из шаблона - <sql:param name="Сумма">1000</sql:param> (показать только тех клиентов, которые сделали хотя бы один заказ на сумму свыше 1000). Если приложение не обеспечивает параметра при вызове шаблона и секция <sql:header> не содержит значение параметра по умолчанию, то для него будет использовано значение по умолчанию, определенное в схеме SQL Server (DEFAULT для поля таблицы или для параметра хранимой процедуры). Если значение по умолчанию в схеме SQL Server также не определено, значение параметра полагается в NULL. Стоит обратить внимание на конвертацию number(Заказы/Заказ/Стоимость) в одном из XPath-запросов в шаблонах рис.6, 7. Дело в том, что несмотря на объявление стоимости как xs:type="xs:float" параметр $Сумма норовит передаваться как nvarchar, что приводит к неверным результатам (напр. '5.00' > '1000'). Еще один, эквивалентный, способ вызова шаблона из SqlXml состоит в использовании свойства CommandStream вместо CommandText. Соответственно должно быть скорректировано свойство CommandType. 


 ...
cmd.CommandStream = new FileStream("..\\Templates\\XMLTemplate1.xml", FileMode.Open, FileAccess.Read);
cmd.CommandType = SqlXmlCommandType.Template;
 ...

Или даже так:


 ...
cmd.CommandStream = new MemoryStream();
  StreamWriter sw = new StreamWriter(cmd.CommandStream, System.Text.Encoding.UTF8);
  sw.Write("<?xml version='1.0' encoding='utf-8' ?> " +
  "<Солянка xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" +
  

...Содержание шаблона с рис.6...
  

  "</Солянка>");
  sw.Flush(); cmd.CommandStream.Position = 0;
  cmd.CommandType = SqlXmlCommandType.Template;
 ...

В свойстве XslPath класса SqlXmlCommand может задаваться ссылка на XSL-преобразование, которому подвергается сформированный на основе шаблона XML-файл. Преобразование можно также оговорить в корневом элементе шаблона (например, sql:xsl="....xsl">).
Для повышения производительности применяется кэширование шаблонов, что означает, что они не выгружаются из памяти после первого выполнения. Емкость кэша (в штуках) задается в HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SQLXML3\TemplateCacheSize и по умолчанию равна 31. Чтобы запретить кэширование, необходимо зайти в закладку Advanced свойств виртуальной директории SQL Server (см. п.13) и отметить Disable Caching of template. Аналогично настраивается кэширование аннотированных схем (HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SQLXML3\SchemaCacheSize).

ПРОДОЛЖЕНИЕ СЛЕДУЕТ

[Содержание]

ФОРУМ SQL.RU

Самые популярные темы недели

Дайте веревку и мыло!
Distributed query, SQL 7.0, SQL 2000
MS SQL2000 и E-Mail
Какой тип данных выбрать
По поводу аппаратной оптимизации SQL-сервера

[Содержание]

Вопросы остались без ответа

Репликация
На кого бы свалить косяк???
Full-text search
SQL Server login packet & (ODBC) ENCRYPT decrypting sp
Проблемы с xp_readmail.
Подскажите как правильно разделить диапазоны значений Identity.
Репликация и odbc

[Содержание]

#100<<  #101
Вопросы, предложения, коментарии, замечания, критику и т.п. присылайте Александру Гладченко на адрес: mssqlhelp@pisem.net
sql.ru
Описание рассылки
 Поиск на SQL.RU
Искать: 
Результатов на странице: Формат:
Тип запроса: Раздел:
МИНИФОРМА
ПОДПИСКИ
http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное