Not
Åtkomst till den här sidan kräver auktorisering. Du kan prova att logga in eller ändra kataloger.
Åtkomst till den här sidan kräver auktorisering. Du kan prova att ändra kataloger.
Gäller för:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric
OPENXML är ett Transact-SQL nyckelord som innehåller en raduppsättning över minnesinterna XML-dokument som liknar en tabell eller en vy. OPENXML ger åtkomst till XML-data som om det vore en relationsraduppsättning. Det gör det genom att tillhandahålla en raduppsättningsvy över den interna representationen av ett XML-dokument. Posterna i raduppsättningen kan lagras i databastabeller.
OPENXML kan användas i SELECT- och SELECT INTO-instruktioner där raduppsättningsprovidrar, en vy eller OPENROWSET kan visas som källa. Information om syntaxen för OPENXML finns i OPENXML (Transact-SQL).
Om du vill skriva frågor mot ett XML-dokument med hjälp av OPENXML måste du först anropa sp_xml_preparedocument. Detta parsar XML-dokumentet och returnerar en referens till det tolkade dokumentet som är redo för förbrukning. Det tolkade dokumentet är en dokumentobjektmodell (DOM) trädrepresentation av olika noder i XML-dokumentet. Dokumenthandtaget skickas till OPENXML. OPENXML tillhandahåller sedan en raduppsättningsvy över dokumentet, baserat på de parametrar som skickas till det.
Anmärkning
sp_xml_preparedocument använder en SQL-uppdaterad version av MSXML-parsern Msxmlsql.dll. Den här versionen av MSXML-parsern har utformats för att stödja SQL Server och vara bakåtkompatibel med MSXML version 2.6.
Den interna representationen av ett XML-dokument måste tas bort från minnet genom att anropa den sp_xml_removedocument system lagrade proceduren för att frigöra minnet.
Följande bild visar processen.
Observera att för att förstå OPENXML krävs kunskaper om XPath-frågor och förståelse för XML. Mer information om XPath-stöd i SQL Server finns i Använda XPath-frågor i SQLXML 4.0.
Anmärkning
Med OpenXML kan XPath-mönster för rad och kolumn parametriseras som variabler. En sådan parameterisering kan leda till XPath-uttrycksinmatningar om programmeraren exponerar parameteriseringen för externa användare (till exempel om parametrarna tillhandahålls via en externt kallad lagrad procedur). För att undvika sådana potentiella säkerhetsproblem rekommenderar vi att XPath-parametrar aldrig exponeras för externa anropare.
Example
I följande exempel visas användningen av OPENXML i en INSERT -instruktion och en SELECT -instruktion. XML-exempeldokumentet innehåller <Customers> och <Orders> element.
Först parsar den sp_xml_preparedocument lagrade proceduren XML-dokumentet. Det tolkade dokumentet är en trädrepresentation av noderna (element, attribut, text och kommentarer) i XML-dokumentet.
OPENXML refererar sedan till det här tolkade XML-dokumentet och ger en raduppsättningsvy över alla eller delar av det här XML-dokumentet. En INSERT instruktion som använder OPENXML kan infoga data från en sådan raduppsättning i en databastabell. Flera OPENXML anrop kan användas för att tillhandahålla en raduppsättningsvy över olika delar av XML-dokumentet och bearbeta dem, till exempel genom att infoga dem i olika tabeller. Den här processen kallas även för fragmentering av XML i tabeller.
I följande exempel strimlades ett XML-dokument på ett sätt som gör att <Customers> element lagras i Customers tabellen och <Orders> element lagras i Orders tabellen med hjälp av två INSERT instruktioner. Exemplet visar också en SELECT instruktion med OPENXML som hämtar CustomerID och OrderDate från XML-dokumentet. Det sista steget i processen är att anropa sp_xml_removedocument. Detta görs för att frigöra det minne som allokerats för att innehålla den interna XML-trädrepresentationen som skapades under parsningsfasen.
-- 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;
Följande bild visar det tolkade XML-trädet i föregående XML-dokument som skapades med hjälp av sp_xml_preparedocument.
OPENXML-parametrar
Parametrarna för OPENXML innehåller följande:
Ett XML-dokumenthandtag (idoc)
Ett XPath-uttryck för att identifiera noderna som ska mappas till rader (radmönster)
En beskrivning av raduppsättningen som ska genereras
Mappning mellan raduppsättningskolumnerna och XML-noderna
XML-dokumenthandtag (idoc)
Dokumenthandtaget returneras av den sp_xml_preparedocument lagrade procedur.
XPath-uttryck för att identifiera de noder som ska bearbetas (rowpattern)
XPath-uttrycket som anges som rowpattern identifierar en uppsättning noder i XML-dokumentet. Varje nod som identifieras av rowpattern motsvarar en enskild rad i raduppsättningen som genereras av OPENXML.
Noderna som identifieras av XPath-uttrycket kan vara valfri XML-nod i XML-dokumentet. Om rowpattern identifierar en uppsättning element i XML-dokumentet finns det en rad i raduppsättningen för varje elementnod som identifieras. Om rowpattern till exempel slutar med ett attribut skapas en rad för varje attributnod som väljs av rowpattern.
Beskrivning av raduppsättningen som ska genereras
Ett raderuppsättningsschema används av OPENXML för att generera den resulterande raduppsättningen. Du kan använda följande alternativ när du anger ett raderuppsättningsschema.
Använd kanttabellformatet
Du bör använda tabellformatet edge för att ange ett raderuppsättningsschema. Använd inte WITH-satsen.
När du gör det returnerar OPENXML en raduppsättning i kanttabellformatet. Detta kallas för en kanttabell eftersom varje kant i det tolkade XML-dokumentträdet mappar till en rad i raduppsättningen.
Edge-tabeller representerar den detaljerade XML-dokumentstrukturen i en enda tabell. Den här strukturen innehåller element- och attributnamnen, dokumenthierarkin, namnrymderna och bearbetningsinstruktionerna. Med tabellformatet edge kan du hämta ytterligare information som inte exponeras via metaegenskaperna. Mer information om metaegenskaper finns i Ange metaegenskaper i OPENXML.
Med den ytterligare information som tillhandahålls av en kanttabell kan du lagra och fråga datatypen för ett element och attribut, och nodtypen, samt lagra och fråga information om XML-dokumentstrukturen. Med den här ytterligare informationen kan det också vara möjligt att skapa ett eget XML-dokumenthanteringssystem.
Med hjälp av en ändtabell kan du skriva lagrade procedurer som tar XML-dokument som binär stort objekt (BLOB) som indata, skapa ändtabellen och sedan extrahera och analysera dokumentet på en mer detaljerad nivå. Den här detaljerade nivån kan omfatta att hitta dokumenthierarkin, element- och attributnamnen, namnrymderna och bearbetningsinstruktionerna.
Kanttabellen kan också fungera som ett lagringsformat för XML-dokument när mappning till andra relationsformat inte är logiskt och ett ntextfält inte tillhandahåller tillräckligt med strukturell information.
I situationer där du kan använda en XML-parser för att undersöka ett XML-dokument kan du använda en kanttabell i stället för att hämta samma information.
I följande tabell beskrivs strukturen för kanttabellen.
| Kolumnnamn | Datatyp | Description |
|---|---|---|
| ID | bigint | Är dokumentnodens unika ID. Rotelementet har ett ID-värde på 0. De negativa ID-värdena är reserverade. |
| parentid | bigint | Identifierar den överordnade noden. Det överordnade objektet som identifieras med det här ID:t är inte nödvändigtvis det överordnade elementet. Detta beror dock på NodeType för noden vars överordnade identifieras av detta ID. Om noden till exempel är en textnod kan dess överordnade vara en attributnod. Om noden är på den översta nivån i XML-dokumentet är dess ParentID NULL. |
| nodtyp | int | Identifierar nodtypen och är ett heltal som motsvarar numreringen av XML-objektmodellen (DOM). Följande är de värden som kan visas i den här kolumnen för att ange nodtypen: 1 = Elementnod 2 = Attributnod 3 = Textnod 4 = CDATA-avsnittsnod 5 = Entitetsreferensnod 6 = Entitetsnod 7 = Bearbetningsinstruktionsnod 8 = Kommentarsnod 9 = Dokumentnod 10 = Nod för dokumenttyp 11 = Nod för dokumentfragment 12 = Notationsnoden Mer information finns i artikeln "nodeType Property" i Microsoft XML (MSXML) SDK. |
| localname | nvarchar(max) | Ger det lokala namnet på elementet eller attributet. Är NULL om DOM-objektet inte har något namn. |
| prefix | nvarchar(max) | Är namnområdesprefixet för nodnamnet. |
| namespaceuri | nvarchar(max) | Är nodens namnområdes-URI. Om värdet är NULL finns inget namnområde. |
| Datatyp | nvarchar(max) | Är den faktiska datatypen för elementet eller attributraden och är annars NULL. Datatypen härleds från den infogade DTD:en eller från det infogade schemat. |
| Föregående | bigint | Är XML-ID för det tidigare syskonelementet. Är NULL om det inte finns något direkt tidigare syskon. |
| text | ntext | Innehåller attributvärdet eller elementinnehållet i textformat. Eller är NULL, om kanttabellposten inte behöver något värde. |
Använd WITH-satsen för att ange en befintlig tabell
Du kan använda WITH-satsen för att ange namnet på en befintlig tabell. Det gör du genom att ange ett befintligt tabellnamn vars schema kan användas av OPENXML för att generera raduppsättningen.
Använd WITH-satsen för att ange ett schema
Du kan använda WITH-satsen för att ange ett fullständigt schema. När du anger raduppsättningsschemat anger du kolumnnamnen, deras datatyper och deras mappning till XML-dokumentet.
Du kan ange kolumnmönstret med hjälp av parametern ColPattern i SchemaDeclaration. Det angivna kolumnmönstret används för att mappa en raduppsättningskolumn till XML-noden som identifieras av rowpattern och som även används för att fastställa typen av mappning.
Om ColPattern inte har angetts för en kolumn mappar raduppsättningskolumnen till XML-noden med samma namn, baserat på mappningen som anges av parametern flags . Men om ColPattern anges som en del av schemaspecifikationen i WITH-satsen skriver den över mappningen som anges i parametern flags .
Mappa mellan raduppsättningskolumnerna och XML-noderna
I OPENXML-instruktionen kan du ange typen av mappning, till exempel attributcentrerad eller elementcentrerad, mellan raduppsättningskolumnerna och XML-noderna som identifieras av radmönstret. Den här informationen används i omvandlingen mellan XML-noderna och raduppsättningskolumnerna.
Du kan ange mappningen på två sätt och du kan också ange båda:
Med hjälp av parametern flags
Mappningen som anges av parametern flags förutsätter namnkorrespondens där XML-noderna mappas till motsvarande raderuppsättningskolumner med samma namn.
Med hjälp av parametern ColPattern
ColPattern, ett XPath-uttryck, anges som en del av SchemaDeclaration i WITH-satsen. Mappningen som anges i ColPattern skriver över mappningen som anges av parametern flags .
ColPattern kan användas för att ange typen av mappning, till exempel attributcentrerad eller elementcentrerad, som skriver över eller förbättrar standardmappningen som anges av flaggorna.
ColPattern anges under följande omständigheter:
Kolumnnamnet i raduppsättningen skiljer sig från det element eller attributnamn som det mappas till. I det här fallet används ColPattern för att identifiera XML-elementet och attributnamnet som raduppsättningskolumnen mappar till.
Du vill mappa ett metaproperty-attribut till kolumnen. I det här fallet används ColPattern för att identifiera den metaproperty som kolumnen rowset mappar till. Mer information om hur du använder metaegenskaper finns i Ange metaegenskaper i OPENXML.
Både flaggorna och ColPattern-parametrarna är valfria. Om ingen mappning anges antas attributcentrerad mappning. Attributcentrerad mappning är standardvärdet för parametern flags .
Attributcentrerad kartläggning
Genom att ange parametern flags i OPENXML till 1 (XML_ATTRIBUTES) angörs attributcentrerad mappning. Om flaggor innehåller XML_ ATTRIBUT tillhandahåller eller använder den exponerade raduppsättningen rader där varje XML-element representeras som en rad. XML-attributen mappas till de attribut som definieras i SchemaDeclaration eller som tillhandahålls av TableName för WITH-satsen baserat på namnkorrespondens. Namnkorrespondens innebär att XML-attributen för ett visst namn lagras i en kolumn i raduppsättningen med samma namn.
Om kolumnnamnet skiljer sig från det attributnamn som det mappar till måste ColPattern anges.
Om XML-attributet har en namnområdeskvalificerare måste kolumnnamnet i raduppsättningen också ha kvalificeraren.
Elementcentrerad kartläggning
Om du anger parametern flags i OPENXML till 2 (XML_ELEMENTS) anges elementcentrerad mappning. Det liknar attributcentrerad mappning, förutom följande skillnader:
Namnkorrespondensen för mappningsexemplet, en kolumnmappning till ett XML-element med samma namn, väljer de icke-komplexa underelementen, om inte ett mönster på kolumnnivå anges. Om underelementet är komplext eftersom det innehåller ytterligare underelement i hämtningsprocessen är kolumnen inställd på NULL. Attributvärdena för underelementen ignoreras sedan.
För flera underelement som har samma namn returneras den första noden.