Bawię się ostatnio publicznymi danymi udostępnionymi z serwisu devPytania (info tutaj O pewnym sukcesie) i już zanim przystąpiłem do próby analizy tych danych pojawił się problem.
Dane zostały udostępnione w formacie XML, który oczywiście mimo wszystkich swoich zalet o przenoszalności i dowolnego jego wykorzystania jest średnio zrozumiały przez MS SQL, które to chciałem zaprząc do tejże analizy. Tak więc pierwszą trudnością było to, jak wczytać takie dane do tabel.
Na sieci można znaleźć sporo postów mówiących, jak rozwiązać ten problem ale większość mówi jak XML’a wczytać po prostu do jednej kolumny bez analizy zawartości i stworzenia tylu wierszy w tabeli, ile głównych node’ów mamy w XMLu.

W końcu po prawie godzinie zmarnowanej na próbowaniu tego czy innego rozwiązania udało się znaleźć działający sposób (OPENXML). Prawie idealny.

DECLARE @idoc int
DECLARE @doc varchar(max)
SET @doc =''
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
INSERT Posts SELECT    *
FROM   OPENXML (@idoc, '/Posts/row',1)
WITH (Id int, PostTypeId int, CreationDate datetime2(7), 
Score int, ViewCount, Body ntext, OwnerUserId int, DeletionDate datetime2(7))

Co tu robimy? Na podstawie zmiennej @doc tworzymy dokument XML a następnie za pomocą OPENXML wybieramy odpowiednie elementy. Parametrami tego polecenia są: dokument XML, XPATH do wzorca wiersza jaki będziemy importować oraz flaga mapowania. Ten ostatni parametr zasługuje na krótkie przyjrzenie się mu. Odpowiada on za ustawienie tego, w jaki sposób będą mapowane dane z XMLa na kolumny w bazie.

0 – domyślnie; takie samo jak 1
1 – dane odczytywane z atrybutów
2 – dane odczytywane z elementów
8 – dane nie będą kopiowane do pola @mp:xmltext, które jest wykorzystywane w celach parsowania

Wartości można łączyć logiczną alternatywą. Czyli wg dokumentacji dozwolone są zarówno wartości 1,2 jak i 3,9 oraz 10.

Po wykonaniu takiego zapytania wiersze z pliku znajdą się w tabeli w bazie danych.
A czemu to prawie idealne rozwiązanie? Z dwóch powodów. Po pierwsze zawartość XML’a trzeba wkleić i podstawić do zmiennej @doc. Nie dałem rady zmusić T-SQL’a do zaczytania pliku i podstawiania wartości do tej zmiennej.
Drugi problem to wielkość danych. varchar to w T-SQL 8000 bajtów, tak więc przy większych plikach (a takowym był plik pytań i odpowiedzi) dane należało podzielić na mniejsze paczki ręcznie. Zmiennej typu text nie da rady utworzyć oczywiście.
Ktoś wie jak można to zrobić lepiej/ładniej/szybciej i ominąć te dwa powody?