OPENXML (SQL Server)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBaza danych SQL w usłudze Microsoft Fabric

OPENXML to słowo kluczowe Transact-SQL, które udostępnia zestaw wierszy w dokumentach XML w pamięci, które są podobne do tabeli lub widoku. Program OPENXML umożliwia dostęp do danych XML tak, jakby był to relacyjny zestaw wierszy. Robi to, udostępniając widok zestawu wierszy wewnętrznej reprezentacji dokumentu XML. Rekordy w zestawie wierszy można przechowywać w tabelach bazy danych.

OpenXML można używać w instrukcjach SELECT i SELECT INTO, gdzie mogą występować dostawcy zestawów wierszy, widok lub OPENROWSET jako źródło. Aby uzyskać informacje o składni openXML, zobacz OPENXML (Transact-SQL).

Aby zapisywać zapytania względem dokumentu XML przy użyciu programu OPENXML, należy najpierw wywołać sp_xml_preparedocument. Spowoduje to przeanalizowanie dokumentu XML i zwrócenie dojścia do przeanalizowanego dokumentu gotowego do użycia. Przeanalizowany dokument to reprezentacja drzewa modelu obiektów dokumentów (DOM) różnych węzłów w dokumencie XML. Dojście dokumentu jest przekazywane do programu OPENXML. Program OPENXML udostępnia następnie widok zestawu wierszy dokumentu na podstawie przekazanych do niego parametrów.

Uwaga / Notatka

sp_xml_preparedocument używa zaktualizowanej wersji sql analizatora MSXML, Msxmlsql.dll. Ta wersja analizatora MSXML została zaprojektowana w celu obsługi SQL Server i zachowania zgodności wstecznej z wersją MSXML 2.6.

Wewnętrzna reprezentacja dokumentu XML musi zostać usunięta z pamięci przez wywołanie procedury składowanej systemu sp_xml_removedocument w celu zwolnienia pamięci.

Poniższa ilustracja przedstawia proces.

Należy pamiętać, że aby zrozumieć język OPENXML, wymagana jest znajomość zapytań XPath i zrozumienie kodu XML. Aby uzyskać więcej informacji na temat obsługi programu XPath w programie SQL Server, zobacz Using XPath Queries in SQLXML 4.0 (Używanie zapytań XPath w programie SQLXML 4.0).

Uwaga / Notatka

Program OpenXML umożliwia sparametryzację wzorców XPath wierszy i kolumn jako zmiennych. Taka parametryzacja może prowadzić do iniekcji wyrażenia XPath, jeśli programista udostępnia parametryzację użytkownikom zewnętrznym (na przykład, jeśli parametry są udostępniane za pośrednictwem zewnętrznie wywołanej procedury składowanej). Aby uniknąć takich potencjalnych problemów z zabezpieczeniami, zaleca się, aby parametry XPath nigdy nie powinny być udostępniane zewnętrznym obiektom wywołującym.

Example

W poniższym przykładzie pokazano użycie instrukcji OPENXML w instrukcji INSERT i instrukcji SELECT . Przykładowy dokument XML zawiera elementy <Customers> i <Orders>.

Najpierw procedura składowana sp_xml_preparedocument analizuje dokument XML. Przeanalizowany dokument jest reprezentacją drzewa węzłów (elementów, atrybutów, tekstu i komentarzy) w dokumencie XML. OPENXML Następnie odwołuje się do tego przeanalizowanego dokumentu XML i udostępnia widok zestawu wierszy dla wszystkich lub części tego dokumentu XML. Instrukcja używająca INSERT polecenia OPENXML może wstawiać dane z takiego zestawu wierszy do tabeli bazy danych. Kilka OPENXML wywołań może służyć do udostępniania widoku zestawu wierszy różnych części dokumentu XML i przetwarzania ich, na przykład przez wstawienie ich do różnych tabel. Ten proces jest również określany jako rozdrabnianie XML do postaci tabel.

W poniższym przykładzie dokument XML jest podzielony w taki sposób, że elementy <Customers> są przechowywane w tabeli Customers, a elementy <Orders> są przechowywane w tabeli Orders przy użyciu dwóch instrukcji INSERT. W przykładzie przedstawiono również instrukcję SELECTOPENXML, która pobiera CustomerID i OrderDate z dokumentu XML. Ostatnim krokiem procesu jest wywołanie metody sp_xml_removedocument. Jest to wykonywane w celu zwolnienia pamięci przydzielonej do przechowywania wewnętrznej reprezentacji drzewa XML, która została utworzona w etapie parsowania.

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20),
                CompanyName varchar(20));
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type
SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@docHandle, N'/ROOT/Customers')
  WITH Customers;
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT *
FROM OPENXML(@docHandle, N'//Orders')
  WITH Orders;
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders')
  WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle;

Poniższa ilustracja przedstawia przeanalizowane drzewo XML poprzedniego dokumentu XML utworzonego przy użyciu sp_xml_preparedocument.

Parametry OPENXML

Parametry programu OPENXML obejmują następujące elementy:

• Uchwyt dokumentu XML (idoc)

• Wyrażenie XPath identyfikujące węzły, które mają być mapowane na wiersze (rowpattern)

• Opis zestawu wierszy do wygenerowania

• Mapowanie między kolumnami zestawu wierszy a węzłami XML

Uchwyt dokumentu XML (idoc)

Wskaźnik do dokumentu jest zwracany przez składowaną procedurę sp_xml_preparedocument.

Wyrażenie XPath umożliwiające zidentyfikowanie węzłów do przetworzenia (rowpattern)

Wyrażenie XPath określone jako rowpattern identyfikuje zestaw węzłów w dokumencie XML. Każdy węzeł, który jest identyfikowany przez rowpattern , odpowiada jednemu wierszowi w zestawie wierszy generowanym przez program OPENXML.

Węzły identyfikowane przez wyrażenie XPath mogą być dowolnym węzłem XML w dokumencie XML. Jeśli rowpattern identyfikuje zestaw elementów w dokumencie XML, istnieje jeden wiersz w zestawie wierszy dla każdego zidentyfikowanego węzła elementu. Jeśli na przykład rowpattern kończy się na atrybucie, tworzony jest wiersz dla każdego węzła atrybutu wybranego przez rowpattern.

Opis zestawu wierszy do wygenerowania

Schemat zestawu wierszy jest używany przez program OPENXML do generowania wynikowego zestawu wierszy. Podczas określania schematu zestawu wierszy można użyć następujących opcji.

Użyj formatu tabeli krawędzi

Aby określić schemat zestawu wierszy, należy użyć formatu tabeli krawędzi. Nie używaj klauzuli WITH.

Gdy to zrobisz, funkcja OPENXML zwraca zestaw wierszy w formacie tabeli krawędzi. Jest to nazywane tabelą krawędzi, ponieważ każda krawędź w przeanalizowanym drzewie dokumentów XML jest mapowana na wiersz w zestawie wierszy.

Tabele krawędzi reprezentują w jednej tabeli szczegółową strukturę dokumentu XML. Ta struktura obejmuje nazwy elementów i atrybutów, hierarchię dokumentów, przestrzenie nazw i instrukcje przetwarzania. Format tabeli krawędzi umożliwia uzyskanie dodatkowych informacji, które nie są widoczne w metawłaściwości. Aby uzyskać więcej informacji na temat metawłaściwości, zobacz Określanie metawłaściwości w programie OPENXML.

Dodatkowe informacje udostępniane przez tabelę krawędzi umożliwiają przechowywanie i wykonywanie zapytań dotyczących typu danych elementu i atrybutu oraz typu węzła, a także przechowywanie i wykonywanie zapytań dotyczących struktury dokumentu XML. Dzięki tym dodatkowym informacjom można również utworzyć własny system zarządzania dokumentami XML.

Korzystając z tabeli brzegowej, można napisać procedury składowane, które przyjmują dokumenty XML jako dane wejściowe dużego obiektu binarnego (BLOB), tworzą tabelę krawędzi, a następnie wyodrębniają i analizują dokument na bardziej szczegółowym poziomie. Ten szczegółowy poziom może obejmować znajdowanie hierarchii dokumentów, nazwy elementów i atrybutów, przestrzeni nazw i instrukcje przetwarzania.

Tabela brzegowa może również służyć jako format przechowywania dokumentów XML, gdy mapowanie na inne formaty relacyjne nie jest logiczne, a pole ntekstowe nie dostarcza wystarczającej ilości informacji strukturalnych.

W sytuacjach, w których można użyć analizatora XML do zbadania dokumentu XML, możesz użyć tabeli krawędzi, aby uzyskać te same informacje.

W poniższej tabeli opisano strukturę tabeli krawędzi.

Nazwa kolumny	Typ danych	Description
id	bigint	Jest unikatowym identyfikatorem węzła dokumentu. Element główny ma wartość identyfikatora 0. Wartości identyfikatorów ujemnych są zarezerwowane.
parentid	bigint	Identyfikuje rodzica węzła. Element zidentyfikowany przez ten identyfikator nie musi być elementem nadrzędnym. Jest to jednak zależne od NodeType węzła, którego rodzic jest identyfikowany przez ten identyfikator. Jeśli na przykład węzeł jest węzłem tekstowym, jego element nadrzędny może być węzłem atrybutu. Jeśli węzeł znajduje się na najwyższym poziomie w dokumencie XML, jego identyfikator ParentID ma wartość NULL.
typ węzła	int	Identyfikuje typ węzła i jest liczbą całkowitą odpowiadającą numerowaniu typu węzła MODELU OBIEKTÓW XML (DOM). Poniżej przedstawiono wartości, które mogą pojawić się w tej kolumnie, aby wskazać typ węzła: 1 = węzeł elementu 2 = węzeł atrybutu 3 = Węzeł tekstowy 4 = węzeł sekcji CDATA 5 = Węzeł odwołania do encji 6 = Węzeł encji 7 = Węzeł instrukcji przetwarzania 8 = Węzeł komentarza 9 = węzeł dokumentu 10 = węzeł Typ dokumentu 11 = węzeł Fragment dokumentu 12 = węzeł notacji Aby uzyskać więcej informacji, zobacz artykuł "nodeType Property" w dokumentacji SDK Microsoft XML (MSXML).
nazwa_lokalna	nvarchar(max)	Nadaje nazwę lokalną elementu lub atrybutu. Ma wartość NULL, jeśli obiekt DOM nie ma nazwy.
przedrostek	nvarchar(max)	Jest prefiksem przestrzeni nazw nazwy węzła.
namespaceuri	nvarchar(max)	Jest URI przestrzeni nazw węzła. Jeśli wartość ma wartość NULL, żadna przestrzeń nazw nie istnieje.
Datatype	nvarchar(max)	Czy rzeczywisty typ danych elementu lub wiersza atrybutu jest inny niż NULL. Typ danych jest wywnioskowany z wbudowanego identyfikatora DTD lub ze schematu wbudowanego.
Poprzedni	bigint	Jest identyfikatorem XML poprzedniego elementu równorzędnego. Ma wartość NULL, jeśli nie ma bezpośredniego poprzedniego elementu równorzędnego.
tekst	ntext	Zawiera wartość atrybutu lub zawartość elementu w formularzu tekstowym. Albo ma wartość NULL, jeśli wpis w tabeli krawędzi nie wymaga wartości.

Użyj klauzuli WITH, aby określić istniejącą tabelę

Możesz użyć klauzuli WITH, aby określić nazwę istniejącej tabeli. W tym celu wystarczy określić istniejącą nazwę tabeli, której schemat może być użyty przez OPENXML do generowania zestawu wierszy.

Użyj klauzuli WITH, aby określić schemat

Możesz użyć klauzuli WITH, aby określić pełny schemat. Określając schemat zestawu wierszy, należy określić nazwy kolumn, ich typy danych i mapowanie na dokument XML.

Wzorzec kolumny można określić przy użyciu parametru ColPattern w schemaDeclaration. Określony wzorzec kolumny służy do mapowania kolumny zestawu wierszy na węzeł XML, który jest identyfikowany przez rowpattern i jest również używany do określania typu mapowania.

Jeśli dla kolumny nie określono ColPattern, kolumna zestawu wierszy jest mapowana na węzeł XML o tej samej nazwie na podstawie mapowania określonego przez parametr flags. Jeśli jednak ColPattern jest określony jako część specyfikacji schematu w klauzuli WITH, zastępuje mapowanie określone w parametrze flags.

Mapuj połączenie między kolumnami zestawu wierszy a węzłami XML

W instrukcji OPENXML można opcjonalnie określić typ mapowania, na przykład skoncentrowany na atrybutach lub skoncentrowany na elementach, między kolumnami zestawu wierszy a węzłami XML, które są identyfikowane przez wzorzec wiersza. Te informacje są używane w transformacji między węzłami XML i kolumnami zestawu wierszy.

Możesz określić mapowanie na dwa sposoby, lub podać oba jednocześnie.

• Przy użyciu parametru flags

  Mapowanie określone przez parametr flags zakłada korespondencję nazw, w której węzły XML są mapowane na odpowiednie kolumny zestawu wierszy o tej samej nazwie.

• Za pomocą parametru ColPattern

  ColPattern, wyrażenie XPath, jest określane jako część SchemaDeclaration w klauzuli WITH. Mapowanie określone w colPattern zastępuje mapowanie określone przez parametr flags .

  ColPattern może być używany do określenia rodzaju mapowania, takiego jak atrybutowe lub elementowe, które zastępuje lub rozszerza domyślne mapowanie wskazywane przez flagi.

  ColPattern jest określony w następujących okolicznościach:

  • Nazwa kolumny w zestawie wierszy różni się od nazwy elementu lub atrybutu, do którego jest mapowana. W takim przypadku ColPattern służy do identyfikowania elementu XML i nazwy atrybutu, do których mapowana jest kolumna zestawu wierszy.

  • Chcesz zamapować atrybut metawłaściwości na kolumnę. W takim przypadku ColPattern służy do identyfikowania metawłaściwości, do której mapowana jest kolumna zestawu wierszy. Aby uzyskać więcej informacji na temat używania metawłaściwości, zobacz Określanie metawłaściwości w programie OPENXML.

Flagi i parametry ColPattern są opcjonalne. Jeśli nie określono mapowania, przyjmuje się mapowanie skoncentrowane na atrybutach. Mapowanie skoncentrowane na atrybutach jest wartością domyślną parametru flags .

Mapowanie skoncentrowane na atrybutach

Ustawienie parametru flags w programie OPENXML na 1 (XML_ATTRIBUTES) określa mapowanie skoncentrowane na atrybutach . Jeśli flagi zawierają XML_ ATRYBUTY , uwidoczniony zestaw wierszy udostępnia lub zużywa wiersze, w których każdy element XML jest reprezentowany jako wiersz. Atrybuty XML są mapowane na atrybuty zdefiniowane w schemaDeclaration lub które są dostarczane przez TableName klauzuli WITH na podstawie korespondencji nazw. Korespondencja nazw oznacza, że atrybuty XML określonej nazwy są przechowywane w kolumnie w zestawie wierszy o tej samej nazwie.

Jeśli nazwa kolumny różni się od nazwy atrybutu, do którego jest mapowany, należy określić ColPattern.

Jeśli atrybut XML ma kwalifikator przestrzeni nazw, nazwa kolumny w zestawie wierszy musi również mieć kwalifikator.

Mapowanie skoncentrowane na elementach

Ustawienie parametru flags w programie OPENXML na 2 (XML_ELEMENTS) określa mapowanie skoncentrowane na elementach . Jest on podobny do mapowania skoncentrowanego na atrybutach , z wyjątkiem następujących różnic:

• W przykładzie mapowania nazwa kolumny mapowana na element XML o tej samej nazwie wybiera nieskomplikowane podelementy, chyba że na poziomie kolumny określono wzorzec. W procesie pobierania, jeśli podelement jest złożony, ponieważ zawiera dodatkowe podelementy, kolumna jest ustawiona na wartość NULL. Następnie wartości atrybutów podelementów są ignorowane.

• W przypadku wielu podelementów o tej samej nazwie zwracany jest pierwszy węzeł.

Zobacz także

• sp_xml_preparedocument (Transact-SQL)
• sp_xml_removedocument (Transact-SQL)
• OPENXML (Transact-SQL)
• danych XML (SQL Server)