Как импортировать конфигурацию Windows Firewall в SQL Server. Часть 2.

Article
06/17/2011

В предыдущем решении (см. Скрипт 9 прошлой серии) есть одно но. В нем используется явный фиксированный список новых колонок Rule_Name, [Enabled], Direction, ... т.е. названия будущих элементов и атрбутов должны быть заранее известны, что применительно к нашему случаю выглядит не совсем гибко. В самом деле, список будущих колонок для свода я взял, посмотрев, из чего состоят первые несколько правил файрвола (см. колонка Name Рис.6 предыдущей серии). Но, во-первых, чем это лучше select … for xml, а во-вторых, кто, действительно, гарантировал, что в колонке Name не появится новое значение (которому должна соответствовать новая колонка в сводной таблице)? К сожалению, оператор pivot умеет оперировать только с фиксированным списком значений for in (…), по крайней мере, на данный момент (2008 R2). Это можно обойти при помощи динамического SQL. Получим в виде строки список всех уникальных значений колонки Name, разделенных запятыми, чтобы сформировать перечень for in (…) для оператора pivot. Я окружил каждое значение квадратными скобками на случай пробелов и других символов, не могущих выступать названиями колонок.

declare @s nvarchar(max) = stuff( (select distinct ', [' + name + ']' from #t order by 1 for xml path('')), 1, 2, '')

select @s

Рис.1

Тогда Скрипт 9 предыдущей серии можно переписать в виде динамического SQL:

if OBJECT_ID('FirewallRules', 'U') is not null drop table FirewallRules

declare @s nvarchar(max) = stuff( (select distinct ', [' + name + ']' from #t order by 1 for xml path('')), 1, 2, '')

exec ( 'select * into FirewallRules from (select ISNULL(parent_id, id) ID, name, value from #t) t pivot (min(value) for name in (' + @s + ')) p' )

Рис.2

И обещаный XML получается, например, непринужденно так:

select * from FirewallRules for xml path('Rule'), root('FirewallRules')

Рис.3

Въедливые читатели заметят, что в Скрипте 8 предыдущего поста имя правила я обещал сделать атрибутом родительского элемента <Rule>, а здесь он получился в виде дочернего элемента Rule_Name. Кроме того, внутри Rule имеется дочерний элемент ID, который я в предыдущем посте вообще не обещал, он как-то сам получился. Надо удалить у всех Rules элемент ID, а элемент Rule_Name перенести в атрибут Name.

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

declare @x xml = '<Persons>

</Person>

</Person>

</Person>

</Persons>'

select @x.query('for $x in Persons/Person[@ID >= "b"] return <Contact ID="{$x/@ID}">{concat($x/Property1[1], "+", $x/Property2[1])}</Contact>')

Рис.4

Я не хочу явно перечислять элементы <Action>, <Direction> и другие свойства файрвольного правила, тем более, что их полный список можно составить, только просмотрев все контейнерные элементы <Rule>. От нас требуется всего лишь удалить один дочерний элемент, а другой сделать атрибутом. Но это нужно сделать для всех содержащих их элементов <Rule>, то есть, по сути, преобразовать схему XML-документа. Что касается преобразования схемы, например, добавления, удаления, переименования и замены элементов и атрибутов, таких возможностей в XQuery 1.0 в явном виде не предусматривается. Они были предложены, в частности, в статье «Extending XQuery with Transformation Operators» и вошли в дополнительную спецификацию XQuery Update Facility, получившую статус Candidate Recommendation (к внесению в XQuery) в марте 2008 г. В SQL Server 2008 эти расширения не поддерживаются. По сравнению с 2005 в нем добавилось, по сути, две функции: upper-case() и lower-case(). Также добавилась поддержка оператора LET, т.е. из акронима FLOWR теперь формально поддерживаются все буквы:

select @x.query('for $x in Persons/Person let $y := concat($x/Property1[1], "+", $x/Property2[1]) where $x/@ID >= "b" order by $x/@ID descending return <Contact ID="{$x/@ID}">{$y}</Contact>')

Рис.5

Хотя, например, сконструировать элемент при помощи let на данный момент в SQL Server не удается:

select @x.query('let $y := (<aaa>1</aaa>) return $y')

Рис.6

Вычисляемые конструкторы также не поддерживаются. Я имею в виду динамическое конструирование узла при помощи ключевых слов element, attribute и т.д. Например, вместо

select cast('' as xml).query('

ddd

ddd1

<aaa2>

ddd2

</aaa2>

</aaa1>

</aaa>

можно написать

select cast('' as xml).query('

element aaa { attribute bbb{"ccc"}, text{"ddd"}, element aaa1{attribute bbb1{"ccc1"}, text{"ddd1"}, element aaa2{"ddd2"} } }

Рис.7

Однако в имени узла на данный момент разрешено использовать только константы. Попытка поставить выражение приводит к ошибке

select cast('' as xml).query('

element {concat("aa", "a")} { attribute bbb{"ccc"}, text{"ddd"}, element aaa1{attribute bbb1{"ccc1"}, text{"ddd1"}, element aaa2{"ddd2"} } }

Рис.8

Рекомендованный обходной путь, как легко догадаться, это снова использовать динамический SQL. Возможности, предоставляемые XQuery для трансформации схемы XML скудны, но и из них поддерживается только подмножество. Список поддерживаемых функций из стандарта XQuery можно посмотреть, например, здесь. В SQL Server 2008 R2 этот список по сравнению с 2008 не увеличился.

Помимо метода query, выполняющего XQuery-запрос, у XMLного типа SQL Server имеется метод modify, который позволяет вставлять и удалять узлы:

declare @x xml = '<Persons>

</Person>

</Person>

</Person>

</Persons>'

set @x.modify('insert attribute Name { "nnn" } into (/Persons/Person)[1]')

set @x.modify('insert element Property3 { "aaa3" } into (/Persons/Person[@ID="aaa"])[1]')

declare @y xml = '<Property0>bbb0</Property0>'

set @x.modify('insert sql:variable("@y") before (/Persons/Person[2]/Property1)[1]')

set @x.modify('delete /Persons/Person[3]/Property1[1]')

set @x.modify('delete /Persons/Person[3]/Property2[1]/text()')

set @x.modify('replace value of (/Persons/Person[2]/@ID)[1] with "bbbbb"')

set @x.modify('replace value of (/Persons/Person[2]/Property1/text())[1] with "bbbbb1"')

select @x

Рис.9

Однако в отличие от кандидатской рекомендации XQuery Update Facility, где операции insert, delete, replace и пр. ориентированы на изменение схемы документа, метод modify изменяет избранные узлы. Он не позволяет добавить или удалить узел сразу у всех элементов XML-документа:

set @x.modify('insert attribute Age { "0" } into /Persons/Person')

------------------------------------

Msg 2226, Level 16, State 1, Line 25

XQuery [modify()]: The target of 'insert' must be a single node, found 'element(Person,xdt:untyped) *'

Здесь предлагаются обходные пути через select for xml или замену подстроки в строковой конвертации XML. Ни тот, ни другой способ не являются полноценной заменой отсутствия встроенных механизмов преобразования структуры XML-документа.

Что касается исходной задачи (Рис.3), требуемые преобразования достаточно просты и могут быть выполнены средствами XQuery:

select cast((select * from FirewallRules for xml path('Rule'), root('FirewallRules')) as XML).query('

<FW>

{ for $rule in FirewallRules/Rule return <Rule Name="{$rule/Rule_Name}">

{ for $ruleprop in $rule/*[position() > 1] where local-name($ruleprop) != "Rule_Name" return $ruleprop

} </Rule>

} </FW>')

Рис.10

В первом цикле $rule пробегается по всем элементам Rule и для каждого на выходе создается элемент Rule с атрибутом Name. Значение этого атрибута берется из дочернего элемента Rule_Name текущего элемента Rule в исходном XML. Вложенный цикл с параметром $ruleprop пробегается по всем дочерним элементам текущего элемента Rule (который сейчас находится в параметре внешнего цикла $rule), после первого. Первым дочерним элементом в каждом Rule идет ID, мы его опускаем, то есть не переносим в результат, то есть, грубо говоря, удаляем. Дополнительным фильтром (where) во внутреннем цикле выступает условие, что если имя дочернего элемента равно «Rule_Name», то мы его тоже пропускаем, потому что его значение мы уже положили в атрибут Name родительского элемента. Все остальные дочерние элементы ($ruleprop) переносятся как есть.

Окончание следует.

Алексей Шуленин

Share via

Как импортировать конфигурацию Windows Firewall в SQL Server. Часть 2.

Additional resources