[PL] Masowy import plików XML w SQL Server 2005 Express Edition
Wczoraj wieczorem kolega zagadnął mnie, czy znam metodę, która pozwoli mu na dokonanie wstawienia zawartości wielu dokumentów XML (dokładnie chodziło mu o dokumenty XSL) do bazy danych zlokalizowanej na instancji SQL Server 2005 Express Edition. Problem polegał na tym, że - po pierwsze - pliki, choć znajdowały się w jednym katalogu, miały dość przypadkowe nazwy (jedyną wspólną cechą było rozszerzenie), których nie można było zmienić, a po drugie - Express Edition nie dysponuje narzędziami Integration Services (poza aplikacją DTSWizard.exe, która nie umożliwia wykonywania takich operacji). Rozwiązanie przyszło mi do głowy w jednej chwili. W sumie kolega trafił szczęśliwie na człowieka (na mnie), który akurat na bieżąco sporo czyta o skryptowaniu (zarówno w PowerShellu, jak i w starym dobrym Command Shellu). Jakie rozwiązanie mu zaproponowałem? Krok po kroku...
Mamy bazę danych (tu posłużę się obrazowo bazą tempdb) z tabelą zawierającą kolumnę typu XML:
USE tempdb
GO
CREATE TABLE dbo.Files
(
FileId int IDENTITY(1,1) PRIMARY KEY,
FileContent XML NOT NULL
)
GO
Importu danych z plików XML w SQL Server 2005 można dokonać używając funkcji OPENROWSET z providerem BULK, np.:
INSERT INTO dbo.Files (FileContent)
SELECT * FROM OPENROWSET(BULK 'C:\xml\plik.xml', SINGLE_NCLOB) T
Problem kolegi sprowadza się zatem do wykonania podanej wyżej składni dla wszystkich plików XML w wybranym katalogu.
Pierwszym krokiem jest zatem zbudowanie skryptu, który byłby wykonywany w pętli i który to skrypt jako parametr przyjmowałby ścieżkę do pliku XML. Z pomocą przychodzi tu SQLCMD i jego naprawdę użyteczne zmienne skryptowe. Skrypt przedstawiać się może następująco:
USE tempdb
GO
INSERT INTO dbo.Files(FileContent)
SELECT * FROM OPENROWSET(BULK $(zmienna), SINGLE_NCLOB) T
Zmienna skryptowa $(zmienna) będzie służyła do przekazywania nazw plików w kolejnych wykonaniach skryptu.
Jak uruchomić taki skrypt, by za każdym razem do zmiennej $(zmienna) została przekazana ścieżka do kolejnego pliku XML? Wykorzystując DOS-ową pętlę FOR oczywiście! Wchodzimy zatem w konsolę (cmd.exe) i po wejściu do katalogu, w którym mamy pliki XML i skrypt T-SQL z wywołaniem OPENROWSET (nazwijmy go przykładowo skrypt.sql), wołamy na przykład:
for %i in (*.xml) do sqlcmd -E -S localhost\SQLEXPRESS -i skrypt.sql -v zmienna="'%~fi'"
Poniżej zrzut ekranu z podobnym wywołaniem (tyle, że nie na wersji Express i nie na instancji nazwanej).
Krótki komentarz do powyższego wywołania SQLCMD w pętli. Do zmiennej %i system będzie w każdej iteracji podstawiał kolejne pliki XML z bieżącego katalogu. Następnie pełna ścieżka (%~fi) do aktualnie podstawionego do zmiennej %i pliku jest podstawiana pod zmienną $(zmienna) w skrypcie T-SQL (dzięki przełącznikowi -v). Proste, ale skuteczne.
W powyższym rozwiązaniu nie ma oczywiście niczego nowatorskiego. Ale przypadek ten potwierdza, że znajomość wielu narzędzi, w tym narzędzi systemu operacyjnego, jest wielce przydatna. Daje swobodę w działaniu i powoduje, że napotkane problemy możemy rozwiązywać na wiele sposobów.
Na koniec mała uwaga. Opcja SINGLE_NCLOB wykorzystana w przykładowych wywołaniach OPENROWSET musi czasem zostać zastąpiona opcją SINGLE_CLOB (na przykład wtedy, gdy pliki były utworzone z wykorzystaniem systemowego notatnika i mają stronę kodową typową dla Windows, a nie używają Unicode).