Быстро загрузить 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

Изучаем свою сеть с NMAP.

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

Это Nmap —аббревиатура от «Network Mapper», дословно переводится  как «сетевой картограф»

Она позволяет детально изучить вашу сеть, проверить открытые порты , запущенные сервисы  и узнать много чего интересного.

Общий синтаксис:

Полное описание команд тут 
Цель сканирования, может быть один хост так и диапазон адресов.

• x-y  по указанной опции nmap 192.168.0-1.1-2 просканирует адреса 192.168.0.1, 192.168.1.1, 192.168.0.2, и 192.168.1.2
• * - то же самое что и 0-255.
• x,y – несколько хостов указываются через запятую. nmap 192.168.0.1,2,4 просканирует 192.168.0.1, 192.168.0.2, и 192.168.0.4.
• /n –позволяет сканировать подсеть. nmap 192.168.0.0/16 выполнит тоже сканирование что и  nmap 192.168.0-255.0-255.

#1: Обнаружение хостов в сети. Сканирование.  

Опции сканирования

-sL (Сканирование с целью составления списка)

-sP (Пинг сканирование)

-PN (Не использовать пинг сканирование)

-PS <список_портов> (TCP SYN пингование)

-PA <список_портов> (TCP ACK пингование)

-PU <список_портов> (UDP пингование)

-PO <список_протоколов> (пингование с использованием IP протокола)

-PR (ARP пингование)

Самый простой способ обнаружить хосты пинг сканирование (ICMP пакеты)

nmap -sP 192.168.0.1-255

Если в сети фаерволл блокирует  ICMP пакеты, в этом случае лучше сразу использовать TCP ACK и TCP Syn  пинг методы для обнаружения удаленных хостов.

nmap -PS 192.168.1.1-255

nmap -PA 192.168.1.1-255

#2: Найти обще используемые TCP порты используя TCP SYN Scan

MDX и T-SQL в одном запросе

Что делать когда нужно объединить в  одном запросе многомерные данные и реляционные ?

1. Чтобы воспользоваться этой возможностью, для начала необходимо создать связанный OLAP сервер.

EXEC master.dbo.sp_addlinkedserver

--имя

@server = N'FinanceOlapServer',

@srvproduct=N'',

--провайдер

@provider=N'MSOLAP',

--сервер

@datasrc=N'localhost',

--многомерная база

@catalog=N'FinanceCube_01'

Теперь воспользуемся функцией OPENQUERY которая выполняет передаваемый запрос к указанному связанному серверу.

Если возникает ошибка The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server.

Делаем по шагам.

1. сначала убираем регистрацию 32 и 64 разрядной DLL

regsvr32 /u "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
regsvr32 /u "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

2.Затем заново регистрируем, сначала 32 битную версию

regsvr32 "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
regsvr32 "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

3. Перезапускаем службу  SQL

2. Использование OPENROWSET и OPENDATASOURCE

(это альтернативный метод для доступа к таблицам на связанном сервере)

По умолчанию в SQL сервер распределенные запросы разрешены через связанный сервер,

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

необходимо активировать соответствующую опцию .

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO

EXEC sp_configure 'ad hoc distributed queries', 1

RECONFIGURE

GO

Полезные запросы SQL

Несколько полезных запросов которые должны быть под рукой у программиста SQL!

1.Склеить  в одну строку данные в столбце

WITH T as

    (SELECT 1 as ID , 'TEST1' as NAME

             UNION ALL 

             SELECT 1 as ID , 'TEST2' as NAME

             UNION ALL 

             SELECT 1 as ID , 'TEST3' as NAME

             UNION ALL 

             SELECT 2 as ID , 'XXX' as NAME

             UNION ALL 

             SELECT 2 as ID , 'YYY' as NAME

             )

      

SELECT DISTINCT T1.ID,

(SELECT STUFF((SELECT ', ' + T2.NAME

    FROM T T2

       WHERE T1.ID  = T2.ID

    FOR XML PATH('')) ,1,1,'')) NAME

FROM T T1

С сортировкой

 with T as
(
select 1 as ID_Сотрудник, N'Здание 1'  as Строение, N'101'  as Номер_комнаты
UNION ALL
select 1 as ID_Сотрудник, N'Здание 2'  as Строение, N'102'  as Номер_комнаты
UNION ALL
select 2 as ID_Сотрудник, N'Здание 2'  as Строение, N'201'  as Номер_комнаты
UNION ALL
select 2 as ID_Сотрудник, N'Здание 1'  as Строение, N'202'  as Номер_комнаты
),

TP as
(
select distinct ID_Сотрудник , Строение from T
)

SELECT DISTINCT T1.ID_Сотрудник,
SUBSTRING(
(
    SELECT ',' + T2.Строение AS 'data()'
        FROM  TP T2
WHERE T1.ID_Сотрудник = T2.ID_Сотрудник
ORDER BY T2.Строение
        FOR XML PATH('')
), 2 , 9999)  Строение,
SUBSTRING(
(
    SELECT ',' + T2.Номер_комнаты AS 'data()'
        FROM  T T2
WHERE T1.ID_Сотрудник = T2.ID_Сотрудник
ORDER BY T2.Строение
        FOR XML PATH('')
), 2 , 9999)  Номер_комнаты
FROM T T1

Начиная с SQL Server 2017 доступна функция STRING_AGG

SELECT STRING_AGG( ISNULL(Строение, ' '), ',') As Строение

From Test

2.Динамический запрос с параметрами

DECLARE @SQLString nvarchar(4000);

DECLARE @ParmDefinition nvarchar(500);

DECLARE @name nvarchar(500);

DECLARE @max_VALUEOUT varchar(30);

SET @name = N'TEST';

SET @SQLString = N'SELECT @max_VALUEOUT = max(ID)

   FROM   (SELECT 1 as ID , ''TEST1'' as NAME UNION ALL  SELECT 8 as ID , ''TEST2'' as NAME   UNION ALL         SELECT 5 as ID , ''TEST3'' as NAME UNION ALL  SELECT 2 as ID , ''XXX'' as NAME) T

   WHERE NAME  like (''%''+@name +''%'')'

SET @ParmDefinition = N'@name varchar(100), @max_VALUEOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @name = @name, @max_VALUEOUT=@max_VALUEOUT OUTPUT;

SELECT @max_VALUEOUT as MAX_VALUE;

Расширенные возможности SQL сервера. Интеграция с CLR.

Есть одна полезная фишка у SQL сервера которая позволяет  использовать собственные библиотеки прямо в SQL сервере это интеграция с CLR.

CLR это промежуточная  исполняющая среда которая переводит  код CIL (промежуточный язык, который генерируют компиляторы)  в байт код.

Таким образом обеспечивается независимость от языка на котором написана программа.

Попробуем подключить собственную библиотеку к SQL серверу.

Создадим DLL, для этого в Visual studio выберем из шаблона тип библиотека классов

 

Опишем методы Sum, Multiply, substract, divide в нашем классе.

Обязательно указываем опцию shared (Указывает, что функции связаны с классом или структурой целиком, а не с определенным экземпляром класса или структуры.)

Веб сервисы WCF

Есть одна технология для создания веб сервисов WCF (Windows Communication Foundation), построена на принципе контрактов  (точно также как фирмы заключают контракты друг с другом, что куда где и как), с помощью этих контрактов описывается формат взаимодействия, данных и сообщений.

Контракты служб [ServiceContract] описывают операции (методы), которые могут выполняться клиентом с помощью службы. Включает контракты необходимых операций [OperationContract] ;

Контракты данных [DataContract] определяют, какие типы данных принимаются и передаются службой. При передаче объекта или структурного типа в параметре операции, в действительности, надо передать лишь его состояние, а принимающая сторона должна преобразовать его обратно к своему родному представлению. Это называется маршалтинг по значению. Он реализуется посредством сериализации, когда пользовательские типы переводятся из CLR представлений в XML содержимое SOAP-конвертов. При  приёме параметров происходит десериализация, т.е. набор XML преобразуется в объект CLR и дальше передаётся для обработки;

Контракты ошибок [FaultContract] определяют, какие исключения инициируются службой, как служба обрабатывает их и передаёт своим клиентам;

Контракты сообщений [MessageContract], [MessageHeader] [MessageBodyHeader] позволяют службам напрямую взаимодействовать с сообщениями и моделировать структуру всего конверта SOAP.

WCF если вкратце это  прокачанный ASMX может все тоже самое плюс дополнительные возможности .

ASMX:

Легко и просто написать и настроить

Поддерживается только IIS

Вызов только по HTTP

WCF :

Большой вариант размещения классический в IIS  плюс  Windows Service , приложение Winforms, консольное приложение – полная свобода (пример хостинга в консольном приложении)

Поддерживает  HTTP (REST и SOAP), TCP/IP, MSMQ и другие протоколы

• HTTP:                                                http://localhost:8001/MyService (в глобальной сети)
• TCP:                                                   net.tcp://localhost:8002/TestService (в лок. сети)
• IPC (именованные каналы):             net.pipe://localhost/TestService (на одном компьютере)
• MSMQ (механизм очередей):         net.msmq://localhost/TestService
• Одноранговые сети:                         net.p2p: (например, узлы GRID)

Улучшена безопасность

Работает быстрее

В общем WCF призван полностью заменить ASMX.  Попробуем это сделать.

SQL и картография

Информацию нужно уметь подавать красиво..

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

Попробуем сделать это за 30 мин.

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

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

При клике на любой регион должна открыться детальная карта этого региона по районам,  в Reporting Services есть возможность сделать подложку карт Bing maps.

Здесь регионы уже будут раскрашены по цветам в зависимости от количества населения в регионе.

Для этого нам понадобится сделать 5 шагов.