Delen via

OPENXML (SQL Server)

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

OPENXML is een Transact-SQL trefwoord, dat een rijset biedt over XML-documenten in het geheugen die vergelijkbaar zijn met een tabel of weergave. OPENXML biedt toegang tot XML-gegevens alsof het een relationele rijenset is. Dit doet u door een rijsetweergave op te geven van de interne weergave van een XML-document. De records in de rijenset kunnen worden opgeslagen in databasetabellen.

OPENXML kan worden toegepast in SELECT- en SELECT INTO-instructies, waar rijensetproviders, een weergave of OPENROWSET als bron kunnen voorkomen. Zie OPENXML (Transact-SQL) voor meer informatie over de syntaxis van OPENXML.

Als u query's wilt schrijven op basis van een XML-document met behulp van OPENXML, moet u eerst sp_xml_preparedocument aanroepen. Hiermee parseert u het XML-document en retourneert u een ingang naar het geparseerde document dat gereed is voor gebruik. Het geparste document is een DOM-structuur (Document Object Model) van verschillende knooppunten in het XML-document. De documenthandle wordt doorgegeven aan OPENXML. OPENXML biedt vervolgens een rijsetweergave van het document op basis van de parameters die eraan zijn doorgegeven.

Opmerking

sp_xml_preparedocument maakt gebruik van een door SQL bijgewerkte versie van de MSXML-parser, Msxmlsql.dll. Deze versie van de MSXML-parser is ontworpen om SQL Server te ondersteunen en achterwaarts compatibel te blijven met MSXML versie 2.6.

De interne weergave van een XML-document moet uit het geheugen worden verwijderd door de sp_xml_removedocument systeem opgeslagen procedure aan te roepen om het geheugen vrij te maken.

In de volgende afbeelding ziet u het proces.

XML parseren met OPENXML.

Houd er rekening mee dat om INZICHT te krijgen in OPENXML, bekendheid met XPath-query's en een goed begrip van XML is vereist. Zie XPath-query's gebruiken in SQLXML 4.0 voor meer informatie over XPath-ondersteuning in SQL Server.

Opmerking

Met OpenXML kunnen XPath-patronen voor rijen en kolommen worden geparameteriseerd als variabelen. Dergelijke parameterisatie kan leiden tot XPath-expressie-injecties, als de programmeur de parameterisatie beschikbaar maakt voor externe gebruikers (bijvoorbeeld als de parameters worden opgegeven via een extern opgeslagen procedure). Om dergelijke potentiële beveiligingsproblemen te voorkomen, wordt aanbevolen dat XPath-parameters nooit worden blootgesteld aan externe bellers.

Example

In het volgende voorbeeld ziet u het gebruik van OPENXML in een INSERT-instructie en een SELECT-instructie. Het XML-voorbeelddocument bevat <Customers> en <Orders> elementen.

Eerst parseert de sp_xml_preparedocument opgeslagen procedure het XML-document. Het geparseerde document is een structuurweergave van de knooppunten (elementen, kenmerken, tekst en opmerkingen) in het XML-document. OPENXML verwijst vervolgens naar dit geparseerde XML-document en biedt een rijsetweergave van alle of delen van dit XML-document. Met een INSERT-instructie OPENXML kunt u gegevens uit een dergelijke rijenset invoegen in een databasetabel. Verschillende OPENXML aanroepen kunnen worden gebruikt om een rijsetweergave van verschillende onderdelen van het XML-document te bieden en ze te verwerken, bijvoorbeeld door ze in verschillende tabellen in te voegen. Dit proces wordt ook wel ‘XML in tabellen verwerken’ genoemd.

In het volgende voorbeeld wordt een XML-document versnipperd op een manier die <Customers> elementen worden opgeslagen in de Customers tabel en <Orders> elementen worden opgeslagen in de Orders tabel met behulp van twee INSERT instructies. In het voorbeeld ziet u ook een SELECT instructie waarmee OPENXMLCustomerID en OrderDate uit het XML-document worden opgehaald. De laatste stap in het proces is het aanroepen sp_xml_removedocument. Dit wordt gedaan om het geheugen vrij te geven dat is toegewezen aan de interne XML-structuurweergave die is gemaakt tijdens de parseringsfase.

-- 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;

In de volgende afbeelding ziet u de geparseerde XML-structuur van het vorige XML-document dat is gemaakt met behulp van sp_xml_preparedocument.

Geparseerde XML-structuur.

OPENXML-parameters

De parameters voor OPENXML omvatten het volgende:

  • Een XML-documenthandgreep (idoc)

  • Een XPath-expressie om de knooppunten te identificeren die moeten worden toegewezen aan rijen (rijpatroon)

  • Een beschrijving van de te genereren rijenset

  • Toewijzing tussen de kolommen van de rijenset en de XML-knooppunten

XML-documentbeheer (idoc)

De documenthandle wordt geretourneerd door de sp_xml_preparedocument stored procedure.

XPath-expressie om de knooppunten te identificeren die moeten worden verwerkt (rijpatroon)

De XPath-expressie die is opgegeven als rijpatroon , identificeert een set knooppunten in het XML-document. Elk knooppunt dat wordt geïdentificeerd door rijpatroon komt overeen met één rij in de rijenset die wordt gegenereerd door OPENXML.

De knooppunten die door de XPath-expressie worden geïdentificeerd, kunnen elk XML-knooppunt in het XML-document zijn. Als rijpatroon een set elementen in het XML-document identificeert, staat er één rij in de rijenset voor elk knooppunt dat wordt geïdentificeerd. Als rijpatroon bijvoorbeeld eindigt op een kenmerk, wordt er een rij gemaakt voor elk kenmerkknooppunt dat is geselecteerd door rowpattern.

Beschrijving van de te genereren rijset

Een rijsetschema wordt door OPENXML gebruikt om de resulterende rijenset te genereren. U kunt de volgende opties gebruiken bij het opgeven van een rijsetschema.

De randtabelindeling gebruiken

U moet de randtabelindeling gebruiken om een rijsetschema op te geven. Gebruik de WITH-component niet.

Wanneer u dit doet, retourneert OPENXML een rijenset in de randtabelindeling. Dit wordt een randtabel genoemd, omdat elke rand in de geparseerde XML-documentstructuur wordt toegewezen aan een rij in de rijenset.

Edge-tabellen vertegenwoordigen binnen één tabel de verfijnde XML-documentstructuur. Deze structuur bevat de element- en kenmerknamen, de documenthiërarchie, de naamruimten en de verwerkingsinstructies. Met de randtabelindeling kunt u aanvullende informatie verkrijgen die niet beschikbaar is via de metaproperties. Zie Metaproperties opgeven in OPENXML voor meer informatie over metaproperties.

Met de aanvullende informatie van een edge-tabel kunt u het gegevenstype van een element en kenmerk en het knooppunttype opslaan en er query's op uitvoeren, en ook informatie over de XML-documentstructuur opslaan en er query's op uitvoeren. Met deze aanvullende informatie kan het ook mogelijk zijn om uw eigen XML-documentbeheersysteem te bouwen.

Met behulp van een randtabel kunt u opgeslagen procedures schrijven die XML-documenten als blob-invoer (binary large object) gebruiken, de randtabel produceren en het document vervolgens op een gedetailleerder niveau extraheren en analyseren. Dit gedetailleerde niveau kan bestaan uit het vinden van de documenthiërarchie, de namen van elementen en kenmerken, de naamruimten en de verwerkingsinstructies.

De randtabel kan ook fungeren als een opslagindeling voor XML-documenten wanneer het toewijzen aan andere relationele indelingen niet logisch is en een ntextveld onvoldoende structurele informatie biedt.

In situaties waarin u een XML-parser kunt gebruiken om een XML-document te onderzoeken, kunt u in plaats daarvan een randtabel gebruiken om dezelfde informatie te verkrijgen.

In de volgende tabel wordt de structuur van de randtabel beschreven.

Kolomnaam Gegevenstype Description
ID bigint Is de unieke id van het documentknooppunt.

Het hoofdelement heeft een id-waarde van 0. De negatieve id-waarden zijn gereserveerd.
parentid bigint Identificeert het bovenliggende element van het knooppunt. Het ouderobject dat door deze ID wordt geïdentificeerd, is niet noodzakelijkerwijs het bovenliggende element. Dit is echter afhankelijk van het NodeType van het knooppunt waarvan het bovenliggende knooppunt wordt geïdentificeerd door deze ID. Als het knooppunt bijvoorbeeld een tekstknooppunt is, kan het bovenliggende knooppunt een kenmerkknooppunt zijn.

Als het knooppunt zich op het hoogste niveau in het XML-document bevindt, is de ParentID NULL.
knooppunttype int Identificeert het knooppunttype en is een geheel getal dat overeenkomt met het DOM-knooppunttypenummering (XML Object Model).

Hier volgen de waarden die in deze kolom kunnen worden weergegeven om het knooppunttype aan te geven:

1 = Elementknooppunt

2 = Kenmerkknooppunt

3 = Tekstknooppunt

4 = CDATA-sectieknooppunt

5 = Entiteitsreferentieknooppunt

6 = Entiteitsknooppunt

7 = Verwerkingsinstructieknooppunt

8 = Opmerkingknooppunt

9 = Documentknooppunt

10 = documenttype-node

11 = Knooppunt Documentfragment

12 = Notatieknooppunt

Zie het artikel nodeType Property in de MICROSOFT XML-SDK (MSXML) voor meer informatie.
localname nvarchar(max) Geeft de lokale naam van het element of kenmerk. Is NULL als het DOM-object geen naam heeft.
voorvoegsel nvarchar(max) Is het naamruimtevoorvoegsel van de naam van het knooppunt.
namespaceuri nvarchar(max) Is de naamruimte-URI van het knooppunt. Als de waarde NULL is, is er geen naamruimte aanwezig.
Datatype nvarchar(max) Is het werkelijke gegevenstype van het element of de kenmerkrij en is anders NULL. Het gegevenstype wordt afgeleid van de inline-DTD of van het inlineschema.
Vorige bigint Is de XML-ID van het vorige sibling-element. Is NULL als er geen directe eerdere broer of zus is.
tekst ntext Bevat de kenmerkwaarde of de elementinhoud in tekstvorm. Of is NULL, als de randtabelvermelding geen waarde nodig heeft.

De WITH-component gebruiken om een bestaande tabel op te geven

U kunt de WITH-component gebruiken om de naam van een bestaande tabel op te geven. Hiervoor hoeft u alleen een bestaande tabelnaam op te geven waarvan het schema door OPENXML kan worden gebruikt om de rijenset te genereren.

De WITH-component gebruiken om een schema op te geven

U kunt de WITH-component gebruiken om een volledig schema op te geven. Bij het opgeven van het rijsetschema geeft u de kolomnamen, de bijbehorende gegevenstypen en de toewijzing aan het XML-document op.

U kunt het kolompatroon opgeven met behulp van de parameter ColPattern in de SchemaDeclaration. Het opgegeven kolompatroon wordt gebruikt om een rijsetkolom toe te wijzen aan het XML-knooppunt dat wordt geïdentificeerd door rijpatroon en wordt ook gebruikt om het type toewijzing te bepalen.

Als ColPattern niet is opgegeven voor een kolom, wordt de rijsetkolom toegewezen aan het XML-knooppunt met dezelfde naam, op basis van de toewijzing die is opgegeven door de parameter vlaggen . Als ColPattern echter is opgegeven als onderdeel van de schemaspecificatie in de WITH-clausule, wordt de toewijzing overschreven die is opgegeven in de parameter flags.

Toewijzen tussen de rijensetkolommen en de XML-knooppunten

In de OPENXML-instructie kunt u desgewenst het type toewijzing opgeven, zoals kenmerkgericht of elementgericht, tussen de rijensetkolommen en de XML-knooppunten die worden geïdentificeerd door de rijpattern. Deze informatie wordt gebruikt in de transformatie tussen de XML-knooppunten en de rijensetkolommen.

U kunt de toewijzing op twee manieren opgeven en u kunt ook beide opgeven:

  • Met behulp van de parameter vlaggen

    Bij de toewijzing die is opgegeven door de parameter vlaggen wordt uitgegaan van een naamovereenkomst waarbij de XML-knooppunten worden toegewezen aan de overeenkomstige rijsetkolommen met dezelfde naam.

  • Met behulp van de parameter ColPattern

    ColPattern, een XPath-expressie, wordt opgegeven als onderdeel van SchemaDeclaration in de WITH-component. De toewijzing die is opgegeven in ColPattern overschrijft de toewijzing die is opgegeven door de parameter vlaggen .

    ColPattern kan worden gebruikt om het type toewijzing op te geven, zoals kenmerkgericht of elementgericht, dat de standaardtoewijzing overschrijft of verbetert die wordt aangegeven door de vlaggen.

    ColPattern wordt opgegeven onder de volgende omstandigheden:

    • De kolomnaam in de rijenset verschilt van het element of de kenmerknaam waaraan deze is toegewezen. In dit geval wordt ColPattern gebruikt om het XML-element en de kenmerknaam te identificeren waaraan de rijsetkolom wordt toegewezen.

    • U wilt een metapropertykenmerk toewijzen aan de kolom. In dit geval wordt ColPattern gebruikt om de metaproperty te identificeren waaraan de rijsetkolom wordt toegewezen. Zie Metaproperties opgeven in OPENXML voor meer informatie over het gebruik van metaproperties.

Zowel de vlaggen als de parameters ColPattern zijn optioneel. Als er geen toewijzing is opgegeven, wordt kenmerkgerichte toewijzing verondersteld. Kenmerkgerichte toewijzing is de standaardwaarde van de parameter flags.

Kenmerkgerichte toewijzing

Het instellen van de parameter flags in OPENXML op 1 (XML_ATTRIBUTES) bepaalt kenmerkgerichte toewijzing. Als vlaggen XML_ KENMERKEN bevatten, levert of verbruikt de weergegeven rijenset rijen waarin elk XML-element wordt weergegeven als een rij. De XML-kenmerken worden toegewezen aan de kenmerken die zijn gedefinieerd in de SchemaDeclaration of die worden geleverd door de TableName van de WITH-clausule, op basis van naamcorrespondentie. Naamcorrespondentie betekent dat de XML-kenmerken van een bepaalde naam worden opgeslagen in een kolom in de rijenverzameling met dezelfde naam.

Als de kolomnaam verschilt van de kenmerknaam waarnaar deze wordt toegewezen, moet ColPattern worden opgegeven.

Als het XML-kenmerk een kwalificatie voor de naamruimte heeft, moet de kolomnaam in de rijenset ook de kwalificatie hebben.

Elementgerichte toewijzing

Het instellen van de parameter vlaggen in OPENXML op 2 (XML_ELEMENTS) specificeert elementgerichte toewijzing. Het is vergelijkbaar met kenmerkgerichte toewijzing, met uitzondering van de volgende verschillen:

  • De naamcorrespondentie in het toewijzingsvoorbeeld: een kolom die wordt toegewezen aan een XML-element met dezelfde naam kiest de niet-complexe subelementen, tenzij er een patroon op kolomniveau is opgegeven. Als het subelement in het ophaalproces complex is omdat het extra subelementen bevat, wordt de kolom ingesteld op NULL. Kenmerkwaarden van de subelementen worden vervolgens genegeerd.

  • Voor meerdere subelementen met dezelfde naam wordt het eerste knooppunt geretourneerd.

Zie ook

  • Last updated on