Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[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).

Opublikowane 15 stycznia 2008 07:55 przez brejk

Komentarze:

# re: Masowy import plików XML w SQL Server 2005 Express Edition

16 stycznia 2008 10:53 by wicherqm@gmail.com

Fajny artykul.

W ramach uzupelnienia

opis "For" na technecie (moze sie przydac jesli ktos bedzie chcial zrobic cos troszeczke innego ale na tej samej zasadzie)

http://technet.microsoft.com/en-us/library/bb490909.aspx

caly Command-line reference

http://technet.microsoft.com/en-us/library/bb491071.aspx

Pozdrawiam

# re: Masowy import plików XML w SQL Server 2005 Express Edition

16 stycznia 2008 13:09 by mgrzeg

Co do 'for'-a i innych polecen systemowych - nie trzeba ich szukac na technecie - sa w pomocy windows. Wystarczy 'F1' i poszukanie 'for', a mamy po polsku ten sam opis, razem z tematami pokrewnymi, etc. Prawda jest taka, ze rzadko kto korzysta z pomocy windows.... ;)

# re: [PL] Masowy import plików XML w SQL Server 2005 Express Edition

10 stycznia 2010 01:23 by danmc

hej,

bardzo przydatnym zastosowaniem dosowego for jest wykonanie pewnej operacji na wielu bazach, albo jeszcze lepiej, na wielu serwerach :)

cmd rulez!

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek