Парсинг XML c помощью powershell

Допустим поступила такая задача, есть много файлов XML

В каждом файле есть нужный атрибут

Необходимо подготовить запрос для выборки из базы

$folderPath= "C:\Shared\BR_Playlist\1\"

$HouseIds= @()

foreach ($item in dir $folderPath)

{

$xmlFilePath = $folderPath + $item.Name

[xml] $xmlContent = [xml] (Get-Content -Path $xmlFilePath)

#Write-Host $item.Name

foreach( $event in $xmlContent.playlist.program.event )

{

    #Write-Host $event.housenumber

    $HouseIds+=$event.housenumber

}

}

#{$_} 

$HouseIds | sort-object | Get-Unique | Select-Object   @{Name="Comnd";Expression={"Select '"+$_+"' as evnt UNION ALL"  }}

Быстро загрузить XML из файла средствами SQL

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

Допустим есть внешний файл следующей структуры.

1.  Загружаем  данные из файла   через OPENROWSET и BULK

2.  Подготавливаем документ к обработке sp_xml_preparedocument

3.  Разбираем файл инструкцией OPENXML

3.  Освобождаем память, удаляем документ sp_xml_removedocument

DECLARE @doc AS XML

SELECT @doc = P

FROM OPENROWSET (BULK 'C:\SHARED\SAMPLEXML.XML',SINGLE_BLOB) AS TEST(P)

DECLARE @hdoc int;

--Процедура sp_xml_preparedocument возвращает дескриптор, по которому можно обратиться к вновь созданному внутреннему представлению XML-документа.

--Считывает входной XML-текст, проводит его синтаксический анализ при помощи средства синтаксического анализатора MSXML (Msxmlsql.dll) и выдает проанализированный документ, готовый к потреблению.

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>';

select @doc

--OPENXML Параметры:

--idoc -Дескриптор документа внутреннего представления XML-документа.

--rowpattern -  Шаблон XPath, используемый для идентификации узлов

--flags  -Указывает на сопоставление, которое должно использоваться между XML-данными и реляционным набором строк

-- 0 по атрибутам; 1 по атрибутам затем по элементам; 2 по элементам затем по атрибутам;

SELECT *

FROM OPENXML(@hdoc,'/SampleXML/row',2)

WITH

    (TITLE NVARCHAR(100) '../@TITLE', --.. путь к вышестоящему элементу ../../ два элемента наверх

       GROUPNAME INT '@GROUP', -- сопоставление с атрибутом задается @

       ID INT,

       NAME NVARCHAR(100)

       )

--Удаляет встроенное представление XML-документа, заданного дескриптором документа и делает недействительным дескриптор документа.

EXEC sp_xml_removedocument @hdoc

SSIS. Веб сервисы на практике.

Допустим нужно реализовать приложение которое будет накапливать информацию о курсах валют ежедневно в нашей БД.

Воспользуемся веб сервисом для получения ежедневных данных.

Создаем SSIS пакет и настраиваем получение данных из веб сервиса, для этого:

1.создаем задачу Web Service

2.Настраиваем, соединение с WSDL файлом

3.Создаем локальный WSDL файл  и жмем кнопку DOWNLOAD WSDL

4.Выбираем нужный нам метод

5. Настраиваем файл результатов xml

Итак мы получили данные  от веб сервиса в виде xml файла, теперь немного теории XML

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

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

Схемы бывают внешние и внутренние, т.е которые определены внутри xml файла.

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

Возьмём, например, простой XML, который содержит ссылки на покупателя и на заказанный продукт. И элементы покупателя, и элементы продукта могут содержать дочерний элемент «ID_number». Ссылки на элемент ID_number будут, таким образом, неоднозначны, у нас будут два одинаковых имени элемента несущих разную смысловую нагрузку и так будет, пока мы не введём пространства имён для их различения.

Пространства имён объявляются с помощью XML атрибута xmlns, значение которого должно быть ссылкой URI.

Как подружить XML и SQL SERVER?

Как подружить XML и  таблицы SQL SERVER, все очень просто

Ниже пример кода по разбору XML тегов, с использованием оператора CROSS APPLY,
полезными будут также примеры для XPATH

  DECLARE @mytable table (RawXml XML)

      

       Insert into @mytable (RawXml)

       select N'

    <Rows>

         <Row OrderNumber="2">

             <Cell ColumnName="agrPerson" Value="77" />

             <Cell ColumnName="norma" Value="" />

             <Cell ColumnName="condition" Value="" />

             <Cell ColumnName="denyDelete" Value="false" />

             <Cell ColumnName="denyMove" Value="false" />

             <Cell ColumnName="denyAdd" Value="" />

             <Cell ColumnName="controlTerm" Value="true" />

<level1>

<tags>

<tag>Green</tag>

<tag>Sams</tag>

<tag>Grass</tag>

</tags>

<level2> </level2>

</level1>

         </Row>

         <Row OrderNumber="3">

             <Cell ColumnName="agrPerson" Value="80" />

             <Cell ColumnName="norma" Value="" />

             <Cell ColumnName="condition" Value="" />

             <Cell ColumnName="denyDelete" Value="false" />

             <Cell ColumnName="denyMove" Value="false" />

             <Cell ColumnName="denyAdd" Value="" />

             <Cell ColumnName="controlTerm" Value="true" />

         </Row>

       </Rows>'

    SELECT

                    x.XmlCol.value('@ColumnName', 'nvarchar(255)') AS ColumnName,

                    x.XmlCol.value('@Value', 'nvarchar(255)') AS Value     

     FROM  @mytable b

       CROSS APPLY b.RawXml.nodes('/Rows/Row/Cell') x(XmlCol)

Более сложный вариант с вложенными элементами

  SELECT

                    row.value('@OrderNumber', 'nvarchar(255)') AS OrderNum,

                    tags.value('.', 'nvarchar(255)') tag,
                    tags.query('.') t     

     FROM  @mytable b

       CROSS APPLY RawXml.nodes('/Rows/Row') AS row( row )
       outer apply row.nodes('level1/tags/tag') tags(tags);

Есть другой вариант с использованием инструкции  OPENXML