OPENXMLKiedyś w pracy w miarę treningu musiałem nauczyć się odczytywać pliki XML w SQL Server.

Oto prosty przykład odczytu pliku XML za pomocą “sp_xml_preparedocument” oraz OPENXML.

Procedura “sp_xml_preparedocument” przygotowuje tekst xml do wykonywania operacji poprzez utworzenie jego wewnętrznej reprezentacji. OPENXML natomiast będzie mógł wyciągnąć wybrane wartości i elementy z pomocą prostej składni.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="Microsoft" ContactName="Cezary Walenciuk">
   <Order CustomerID="Microsoft" EmployeeID="5" OrderDate="1999-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
    <Comment>I need this fast</Comment>
</Customer>
<Customer CustomerID="Google" ContactName="Adam Adamowski">
   <Order CustomerID="Google" EmployeeID="3" OrderDate="2006-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
   <Comment>Just testing?</Comment>
</Customer>
</ROOT>'
--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.
SELECT    *
FROM      OPENXML (@idoc, '/ROOT/Customer',1)
            WITH (CustomerID  VARCHAR(10),
                  ContactName VARCHAR(20),
			EmployeeID  INT 'Order/@EmployeeID',
                  OrderDate DATETIME 'Order/@OrderDate',    
                  OrderID  INT 'Order/OrderDetail/@OrderID',
                  ProductID INT 'Order/OrderDetail/@ProductID',
                  Comment  VARCHAR(50)'Comment')

Wynik zapytania:

image

Zmienna “@idoc” jest tak jakby wskaźnikiem do dokumentu XML, który został w wygenerowany w procedurze sp_xml_preparedocument. Po wykonaniu prostego select został zwrócony cały plik XML.

image

Jednak po operacji OPENXML zostają zwracane różne wyniki liczbowe. Jednym słowem egzystencja zmiennej  “@idoc” jest przeznaczona do użytku w słowie kluczowym OPENXML.

Przejdź więc do OPENXML-a. Na początku podajemy zmienną “@idoc” potem podajemy wyrażenie XPATH, które określa nam, po jakiej ścieżce chcemy wyciągać elementy w pliku XML.

Ostatni parametr jest opcjonalny. Jest to flaga bitowa określający styl mapowania tekstu XML do kolumn bazo danowych. W tym wypadku wybrałem flagę bitową 1 ,ale w tym prostym przypadku zmiana flagi bitowej nie ma żadnego skutku ubocznego w wyniku zapytania.

Istnieje więcej parametrów dla operacji OPENXML http://msdn.microsoft.com/en-us/library/aa276847%28v=sql.80%29.aspx

Po słowie WITH podaje elementy i atrybuty, które będę wyciągał. Jeśli atrybuty odnoszą się do oryginalnej ścieżki, która została podane w OPENXML wystarczy  nazwać kolumnę tak jak dany atrybut (CustomerID, ContactName).

Jeśli chce odwołać się do atrybutu, który znajdują się głębiej w drzewie XML muszę podać do niego ścieżkę XPATH. Muszę też przed nazwą dodać znaczek “@” aby podkreślić ,że chodzi mi o atrybut znajdujący się w tym elemencie ,a nie o kolejną pod gałąź o tej nazwie.

Jak więc wyciągnąć wartość tekstową, która znajduje się pod  elementem. Wystarczy tylko na niego wskazać (Comment).