Exempel: Använda OPENXML

Gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance SQL-databas i Microsoft Fabric

Exemplen i den här artikeln visar hur OPENXML används för att skapa en raduppsättningsvy av ett XML-dokument. Information om syntaxen för OPENXML finns i OPENXML (Transact-SQL). Exemplen visar alla aspekter av OPENXML, men ange inte metaegenskaper i OPENXML. Mer information om hur du anger metaegenskaper i OPENXML finns i Ange metaegenskaper i OPENXML.

Examples

När du hämtar data används rowpattern för att identifiera noderna i XML-dokumentet som avgör raderna. Dessutom uttrycks rowpattern i XPath-mönsterspråket som används i MSXML XPath-implementeringen. Om mönstret till exempel slutar i ett element eller ett attribut skapas en rad för varje element eller attributnod som väljs av rowpattern.

Värdet för flaggor tillhandahåller standardmappning. Om ingen ColPattern anges i SchemaDeclaration antas mappningen som anges i flaggor . Värdet för flaggor ignoreras om ColPattern anges i SchemaDeclaration. Den angivna ColPattern avgör mappningen, attributcentrerad eller elementcentrerad, samt beteendet vid hantering av överflöde och okonsumerade data.

A. Köra en SELECT-instruktion med OPENXML

XML-dokumentet i det här exemplet består av elementen <Customer>, <Order>och <OrderDetail> . OPENXML-instruktionen hämtar kundinformation i en raduppsättning med två kolumner, CustomerID och ContactName, från XML-dokumentet.

Först anropas den sp_xml_preparedocument lagrade proceduren för att hämta ett dokumenthandtag. Detta dokumenthandtag passeras till OPENXML.

OPENXML-instruktionen illustrerar följande:

rowpattern (/ROOT/Customer) identifierar de noder som <Customer> ska bearbetas.
Parametervärdet flags är inställt på 1 och anger attributcentrerad mappning. Därför mappas XML-attributen till kolumnerna i raduppsättningen som definierats i SchemaDeclaration.
I SchemaDeclaration i WITH-satsen matchar de angivna ColName-värdena motsvarande XML-attributnamn. Därför anges inte ColPattern-parametern i SchemaDeclaration.

SELECT-instruktionen hämtar sedan alla kolumner i raduppsättningen som tillhandahålls av OPENXML.

DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
          OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
          OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
      WITH (CustomerID  varchar(10),
            ContactName varchar(20));
EXEC sp_xml_removedocument @DocHandle;

Det här är resultatet:

CustomerID ContactName
---------- --------------------
VINET      Paul Henriot
LILAS      Carlos Gonzalez

Eftersom elementen <Customer> inte har några underelement returneras värdena för CustomerID och ContactName för båda kunderna som NULL om samma SELECT-instruktion körs med flaggorna2 för att indikera elementcentrerad mappning.

@xmlDocument kan också vara av xml-typ eller av (n)varchar(max) -typ.

Om <CustomerID> och <ContactName> i XML-dokumentet är underelement hämtar den elementcentrerade mappningen värdena.

DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer>
   <CustomerID>VINET</CustomerID>
   <ContactName>Paul Henriot</ContactName>
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer>
   <CustomerID>LILAS</CustomerID>
   <ContactName>Carlos Gonzalez</ContactName>
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT    *
FROM      OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
           WITH (CustomerID  varchar(10),
                 ContactName varchar(20));
EXEC sp_xml_removedocument @XmlDocumentHandle;

Det här är resultatet:

CustomerID ContactName
---------- --------------------
VINET      Paul Henriot
LILAS      Carlos Gonzalez

Dokumenthandtaget som returneras av sp_xml_preparedocument är giltigt under batchen och inte sessionen.

B. Ange ColPattern för mappning mellan raderuppsättningskolumner och XML-attribut och -element

Det här exemplet visar hur XPath-mönstret anges i den valfria ColPattern-parametern för att tillhandahålla mappning mellan raderuppsättningskolumner och XML-attribut och -element.

XML-dokumentet i det här exemplet består av elementen <Customer>, <Order>och <OrderDetail> . OPENXML-instruktionen hämtar kund- och orderinformation som en raduppsättning (CustomerID, OrderDate, ProdID och Qty) från XML-dokumentet.

Först anropas den sp_xml_preparedocument lagrade proceduren för att hämta ett dokumenthandtag. Detta dokumenthandtag passeras till OPENXML.

OPENXML-instruktionen illustrerar följande:

rowpattern (/ROOT/Customer/Order/OrderDetail) identifierar de noder som <OrderDetail> ska bearbetas.

Som illustration anges parametervärdet för flaggor till 2 och anger elementcentrerad mappning. Mappningen som anges i ColPattern skriver dock över den här mappningen. XPath-mönstret som anges i ColPattern mappar alltså kolumnerna i raduppsättningen till attribut. Detta resulterar i attributcentrerad mappning.

I SchemaDeclaration i WITH-satsen anges ColPattern också med parametrarna ColName och ColType . Det valfria ColPattern är det angivna XPath-mönstret och anger följande:

Kolumnerna OrderID, CustomerID och OrderDate i raduppsättningen mappar till attributen för de överordnade noderna som identifieras av rowpattern, och rowpattern identifierar <OrderDetail> noderna. Därför mappas kolumnerna CustomerID och OrderDate till attributen CustomerID och OrderDate för elementet <Order> .
Kolumnerna ProdID och Qty i raduppsättningen mappar till attributen ProductID och Quantity för noderna som identifieras i rowpattern.

SELECT-instruktionen hämtar sedan alla kolumner i raduppsättningen som tillhandahålls av OPENXML.

DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID     int         '../@OrderID',
      CustomerID  varchar(10) '../@CustomerID',
      OrderDate   datetime    '../@OrderDate',
      ProdID      int         '@ProductID',
      Qty         int         '@Quantity');
EXEC sp_xml_removedocument @XmlDocumentHandle;

Det här är resultatet:

OrderID CustomerID        OrderDate          ProdID    Qty
-------------------------------------------------------------
10248    VINET     1996-07-04 00:00:00.000     11       12
10248    VINET     1996-07-04 00:00:00.000     42       10
10283    LILAS     1996-08-16 00:00:00.000     72        3

XPath-mönstret som anges som ColPattern kan också anges för att mappa XML-elementen till raduppsättningskolumnerna. Detta resulterar i elementcentrerad mappning. I följande exempel är XML-dokumentet <CustomerID> och <OrderDate> underelement i elementet <Orders> . Eftersom ColPattern skriver över mappningen som anges i parametern flags anges inte parametern flags i OPENXML.

DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order EmployeeID="5" >
      <OrderID>10248</OrderID>
      <CustomerID>VINET</CustomerID>
      <OrderDate>1996-07-04T00:00:00</OrderDate>
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order  EmployeeID="3" >
      <OrderID>10283</OrderID>
      <CustomerID>LILAS</CustomerID>
      <OrderDate>1996-08-16T00:00:00</OrderDate>
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID  varchar(10)   '../CustomerID',
      OrderDate   datetime      '../OrderDate',
      ProdID      int           '@ProductID',
      Qty         int           '@Quantity');
EXEC sp_xml_removedocument @docHandle;

C. Kombinera attributcentrerad och elementcentrerad mappning

I det här exemplet är parametern flags inställd på 3 och anger att både attributcentrerad och elementcentrerad mappning kommer att tillämpas. I det här fallet tillämpas den attributcentrerade mappningen först och sedan tillämpas elementcentrerad mappning för alla kolumner som ännu inte har behandlats.

DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument =N'<ROOT>
<Customer CustomerID="VINET"  >
     <ContactName>Paul Henriot</ContactName>
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
          OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" >
     <ContactName>Carlos Gonzalez</ContactName>
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
          OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer',3)
      WITH (CustomerID  varchar(10),
            ContactName varchar(20));
EXEC sp_xml_removedocument @docHandle;

Det här är resultatet

CustomerID ContactName
---------- --------------------
VINET      Paul Henriot
LILAS      Carlos Gonzalez

Den attributcentrerade mappningen används för CustomerID. Det finns inget ContactName-attribut i elementet <Customer> . Därför tillämpas elementcentrerad mappning.

D. Ange XPath-funktionen text() som ColPattern

XML-dokumentet i det här exemplet består av elementen <Customer> och <Order> . OPENXML-instruktionen hämtar en raduppsättning som består av oid-attributet från elementet <Order> , ID:t för den överordnade noden som identifieras av rowpattern och lövvärdessträngen för elementinnehållet.

Först anropas den sp_xml_preparedocument lagrade proceduren för att hämta ett dokumenthandtag. Detta dokumenthandtag passeras till OPENXML.

OPENXML-instruktionen illustrerar följande:

rowpattern (/root/Customer/Order) identifierar de noder som <Order> ska bearbetas.
Parametervärdet flags är inställt på 1 och anger attributcentrerad mappning. Därför kommer XML-attributen att mappas till de radsättkolumner som definierats i SchemaDeclaration.
I SchemaDeclaration i WITH-satsen matchar kolumnnamnen oid och amount rowset motsvarande XML-attributnamn. Därför anges inte ColPattern-parametern . För kommentarskolumnen i raduppsättningen anges XPath-funktionen , text()som ColPattern. Detta skriver över den attributcentrerade mappning som anges i flaggor och kolumnen innehåller lövvärdessträngen för elementinnehållet.

SELECT-instruktionen hämtar sedan alla kolumner i raduppsättningen som tillhandahålls av OPENXML.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--sample XML document
SET @xmlDocument =N'<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
      </Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
             white red">
            <Urgency>Important</Urgency>
            Happy Customer.
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
     WITH (oid     char(5),
           amount  float,
           comment ntext 'text()');
EXEC sp_xml_removedocument @docHandle;

Det här är resultatet:

oid   amount        comment
----- -----------   -----------------------------
O1    3.5           NULL
O2    13.4          Customer was very satisfied
O3    100.0         Happy Customer.
O4    10000.0       NULL

E. Ange TableName i WITH-satsen

Det här exemplet anger TableName i WITH-satsen i stället för SchemaDeclaration. Detta är användbart om du har en tabell som har den struktur du vill ha och inga kolumnmönster, ColPattern-parametern , krävs.

XML-dokumentet i det här exemplet består av elementen <Customer> och <Order> . OPENXML-instruktionen hämtar orderinformation i en raduppsättning med tre kolumner (oid, datum och belopp) från XML-dokumentet.

Först anropas den sp_xml_preparedocument lagrade proceduren för att hämta ett dokumenthandtag. Detta dokumenthandtag passeras till OPENXML.

OPENXML-instruktionen illustrerar följande:

rowpattern (/root/Customer/Order) identifierar de noder som <Order> ska bearbetas.
Det finns ingen SchemaDeclaration i WITH-satsen. I stället anges ett tabellnamn. Därför används tabellschemat som raderuppsättningsschema.
Parametervärdet flags är inställt på 1 och anger attributcentrerad mappning. Därför mappas attributen för elementen, som identifieras av rowpattern, till raduppsättningskolumnerna med samma namn.

SELECT-instruktionen hämtar sedan alla kolumner i raduppsättningen som tillhandahålls av OPENXML.

-- Create a test table. This table schema is used by OPENXML as the
-- rowset schema.
CREATE TABLE T1(oid char(5), date datetime, amount float);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
-- Sample XML document
SET @xmlDocument =N'<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very
             satisfied</Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
             white red">
          <Urgency>Important</Urgency>
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
     WITH T1;
EXEC sp_xml_removedocument @docHandle;

Det här är resultatet:

oid   date                        amount
----- --------------------------- ----------
O1    1996-01-20 00:00:00.000     3.5
O2    1997-04-30 00:00:00.000     13.4
O3    1999-07-14 00:00:00.000     100.0
O4    1996-01-20 00:00:00.000     10000.0

F. Hämta resultatet i ett kanttabellformat

I det här exemplet anges inte WITH-satsen i OPENXML-instruktionen. Därför har raduppsättningen som genereras av OPENXML ett kanttabellformat. SELECT-instruktionen returnerar alla kolumner i kanttabellen.

XML-exempeldokumentet i exemplet består av elementen <Customer>, <Order>och <OrderDetail> .

Först anropas den sp_xml_preparedocument lagrade proceduren för att hämta ett dokumenthandtag. Detta dokumenthandtag passeras till OPENXML.

OPENXML-instruktionen illustrerar följande:

rowpattern (/ROOT/Customer) identifierar de noder som <Customer> ska bearbetas.
WITH-satsen är inte tillgänglig. Därför returnerar OPENXML raduppsättningen i ett kanttabellformat.

SELECT-instruktionen hämtar sedan alla kolumner i kanttabellen.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
SET @xmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate=
           "1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer');

EXEC sp_xml_removedocument @docHandle;

Resultatet returneras som en kanttabell. Du kan skriva frågor mot gränstabellen för att hämta information. Till exempel:

Följande fråga returnerar antalet kundnoder i dokumentet. Eftersom WITH-satsen inte har angetts returnerar OPENXML en gränstabell. SELECT-instruktionen frågar edge-tabellen.
```
SELECT count(*)
FROM OPENXML(@docHandle, '/')
WHERE localname = 'Customer';
```

Följande fråga returnerar de lokala namnen på XML-noder av elementtyp.

SELECT distinct localname
FROM OPENXML(@docHandle, '/')
WHERE nodetype = 1
ORDER BY localname;

G. Ange radmönster som slutar med ett attribut

XML-dokumentet i det här exemplet består av elementen <Customer>, <Order>och <OrderDetail> . OPENXML-instruktionen hämtar information om orderinformationen i en raduppsättning med tre kolumner (ProductID, Quantity och OrderID) från XML-dokumentet.

Först anropas sp_xml_preparedocument för att hämta ett dokumenthandtag. Detta dokumenthandtag passeras till OPENXML.

OPENXML-instruktionen illustrerar följande:

rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) slutar med ett XML-attribut, ProductID. I den resulterande raduppsättningen skapas en rad för varje attributnod som valts i XML-dokumentet.
I det här exemplet anges inte parametern flags . Mappningarna anges i stället av parametern ColPattern .

I SchemaDeclaration i WITH-satsen anges ColPattern också med parametrarna ColName och ColType . Det valfria ColPattern är XPath-mönstret som anges för att ange följande:

XPath-mönstret (.) som anges som ColPattern för kolumnen ProdID i raduppsättningen identifierar kontextnoden, den aktuella noden. Enligt det angivna rowpattern är det ProductID-attributet för <OrderDetail>-elementet.
ColPattern, .. /@Quantity som anges för kolumnen Qty i raduppsättningen identifierar attributet Quantity för den överordnade noden, <OrderDetail>, noden för kontextnoden <ProductID>.
På samma sätt , ColPattern, .. /.. /@OrderID som anges för kolumnen OID i raduppsättningen identifierar attributet OrderID för den överordnade noden, <Order>, för kontextnodens överordnade nod. Den överordnade noden är <OrderDetail>, och kontextnoden är <ProductID>.

SELECT-instruktionen hämtar sedan alla kolumner i raduppsättningen som tillhandahålls av OPENXML.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--Sample XML document
SET @xmlDocument =N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate=
           "1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')
       WITH ( ProdID  int '.',
              Qty     int '../@Quantity',
              OID     int '../../@OrderID');
EXEC sp_xml_removedocument @docHandle;

Det här är resultatet:

ProdID      Qty         OID
----------- ----------- -------
11          12          10248
42          10          10248
72          3           10283

H. Ange ett XML-dokument som har flera textnoder

Om du har flera textnoder i ett XML-dokument returnerar en SELECT-instruktion med en ColPattern, text(), endast den första textnoden i stället för alla. Till exempel:

DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT,
         N'<root xmlns:a="urn:1">
           <a:Elem abar="asdf">
             T<a>a</a>U
           </a:Elem>
         </root>',
         '<ns xmlns:b="urn:1" />';

SELECT * FROM openxml(@h, '/root/b:Elem')
      WITH (Col1 varchar(20) 'text()');
EXEC sp_xml_removedocument @h;

SELECT-instruktionen returnerar T som resultat och inte TaU.

I. Ange XML-datatypen i WITH-satsen

I WITH-satsen måste ett kolumnmönster som mappas till kolumnen xml-datatyp , oavsett om det är skrivet eller otypat, returnera antingen en tom sekvens eller en sekvens med element, bearbetningsinstruktioner, textnoder och kommentarer. Data omvandlas till en XML-datatyp .

I följande exempel innehåller tabellschemadeklarationen i WITH-satsen kolumner av xml-typ .

DECLARE @h int;
DECLARE @x xml;
set @x = '<Root>
  <row id="1"><lname>Duffy</lname>
   <Address>
            <Street>111 Maple</Street>
            <City>Seattle</City>
   </Address>
  </row>
  <row id="2"><lname>Wang</lname>
   <Address>
            <Street>222 Pine</Street>
            <City>Bothell</City>
   </Address>
  </row>
</Root>';

EXEC sp_xml_preparedocument @h output, @x;
SELECT *
FROM   OPENXML (@h, '/Root/row', 10)
      WITH (id int '@id',

            lname    varchar(30),
            xmlname  xml 'lname',
            OverFlow xml '@mp:xmltext');
EXEC sp_xml_removedocument @h;

Mer specifikt skickar du en xml-typvariabel (@x) till funktionen sp_xml_preparedocument().

Det här är resultatet:

id  lname   xmlname                   OverFlow
--- ------- ------------------------------ -------------------------------
1   Duffy   <lname>Duffy</lname>  <row><Address>
                                   <Street>111 Maple</Street>
                                   <City>Seattle</City>
                                  </Address></row>
2   Wang    <lname>Wang</lname>   <row><Address>
                                    <Street>222 Pine</Street>
                                    <City>Bothell</City>
                                   </Address></row>

Observera följande från resultatet:

För kolumnen lname av typen varchar(30) hämtas dess värde från motsvarande <lname> element.
För xmlname-kolumnen av xml-typ returneras samma namnelement som dess värde.
Flaggan är inställd på 10. 10 betyder 2 + 8, där 2 anger elementcentrerad mappning och 8 anger att endast okonsumerade XML-data ska läggas till i kolumnen OverFlow som definieras i WITH-klausulen. Om du anger flaggan till 2 kopieras hela XML-dokumentet till kolumnen OverFlow som anges i WITH-satsen.
Om kolumnen i WITH-satsen är en typinskriven XML-kolumn och XML-instansen inte bekräftar schemat returneras ett fel.

J. Hämta enskilda värden från flervärdesattribut

Ett XML-dokument kan ha attribut som är flervärdes. IDREFS-attributet kan till exempel vara flervärdesattribut. I ett XML-dokument anges värden för flervärdesattribut som en sträng, med värdena avgränsade med ett blanksteg. I följande XML-dokument är attributet attends för elementet <Student> och attributet attendedBy för <Class> flervärda. Att hämta enskilda värden från ett xml-attribut med flera värden och lagra varje värde på en separat rad i databasen kräver extra arbete. Det här exemplet visar processen.

Det här XML-exempeldokumentet består av följande element:

<Student>

ID (student-ID), namn och närvaro attribut. Attributet attends är ett flervärdesattribut.
<Klass>

Attributen id (klass-ID), namn och attendedBy. Attributet attendedBy är ett flervärdesattribut.

Attributet attends i <Student> och attributet attendedBy i <Class> representerar en m:n-relation mellan tabellerna Student och Klass. En elev kan ta många klasser och en klass kan ha många studenter.

Anta att du vill strimla det här dokumentet och spara det i databasen enligt följande:

<Student> Spara data i tabellen Studenter.
Spara <Class>-data i tabellen Kurser.
Spara relationsdata för m:n , mellan student och klass, i tabellen CourseAttendence. Mer arbete krävs för att extrahera värdena. Om du vill hämta den här informationen och lagra den i tabellen använder du följande lagrade procedurer:
- Infoga_Idrefs_Värden
  
  Infogar värdena för kurs-ID och elev-ID i tabellen CourseAttendence.
- Extrahera_idrefs_värden
  
  Extraherar de enskilda student-ID:na från varje <kurselement> . En kanttabell används för att hämta dessa värden.

Här är stegen:

-- Create these tables:
DROP TABLE CourseAttendance;
DROP TABLE Students;
DROP TABLE Courses;
GO
CREATE TABLE Students(
                id   varchar(5) primary key,
                name varchar(30)
                );
GO
CREATE TABLE Courses(
               id       varchar(5) primary key,
               name     varchar(30),
               taughtBy varchar(5)
);
GO
CREATE TABLE CourseAttendance(
             id         varchar(5) references Courses(id),
             attendedBy varchar(5) references Students(id),
             constraint CourseAttendance_PK primary key (id, attendedBy)
);
GO
-- Create these stored procedures:
DROP PROCEDURE f_idrefs;
GO
CREATE PROCEDURE f_idrefs
    @t      varchar(500),
    @idtab  varchar(50),
    @id     varchar(5)
AS
DECLARE @sp int;
DECLARE @att varchar(5);
SET @sp = 0;
WHILE (LEN(@t) > 0)
BEGIN
    SET @sp = CHARINDEX(' ', @t+ ' ');
    SET @att = LEFT(@t, @sp-1);
    EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')');
    SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp);
END;
GO

DROP PROCEDURE fill_idrefs
GO
CREATE PROCEDURE fill_idrefs
    @xmldoc     int,
    @xpath      varchar(100),
    @from       varchar(50),
    @to         varchar(50),
    @idtable    varchar(100)
AS
DECLARE @t varchar(500);
DECLARE @id varchar(5);

/* Temporary Edge table */
SELECT *
INTO #TempEdge
FROM OPENXML(@xmldoc, @xpath);

DECLARE fillidrefs_cursor CURSOR FOR
    SELECT CAST(iv.text AS nvarchar(200)) AS id,
           CAST(av.text AS nvarchar(4000)) AS refs
    FROM   #TempEdge c, #TempEdge i,
           #TempEdge iv, #TempEdge a, #TempEdge av
    WHERE  c.id = i.parentid
    AND    UPPER(i.localname) = UPPER(@from)
    AND    i.id = iv.parentid
    AND    c.id = a.parentid
    AND    UPPER(a.localname) = UPPER(@to)
    AND    a.id = av.parentid;

OPEN fillidrefs_cursor
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
        execute f_idrefs @t, @idtable, @id;
    END
    FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
END;
CLOSE fillidrefs_cursor;
DEALLOCATE fillidrefs_cursor;
Go
-- This is the sample document that is shredded and the data is stored in the preceding tables.
DECLARE @h int;
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>
  <Student id = "s1" name = "Student1"  attends = "c1 c3 c6"  />
  <Student id = "s2" name = "Student2"  attends = "c2 c4" />
  <Student id = "s3" name = "Student3"  attends = "c2 c4 c6" />
  <Student id = "s4" name = "Student4"  attends = "c1 c3 c5" />
  <Student id = "s5" name = "Student5"  attends = "c1 c3 c5 c6" />
  <Student id = "s6" name = "Student6" />

  <Class id = "c1" name = "Intro to Programming"
         attendedBy = "s1 s4 s5" />
  <Class id = "c2" name = "Databases"
         attendedBy = "s2 s3" />
  <Class id = "c3" name = "Operating Systems"
         attendedBy = "s1 s4 s5" />
  <Class id = "c4" name = "Networks" attendedBy = "s2 s3" />
  <Class id = "c5" name = "Algorithms and Graphs"
         attendedBy =  "s4 s5"/>
  <Class id = "c6" name = "Power and Pragmatism"
         attendedBy = "s1 s3 s5" />
</Data>';

INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students

INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses
/* Using the edge table */
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance';

SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM CourseAttendance;

EXECUTE sp_xml_removedocument @h;

K. Hämta binära data från base64-kodade data i XML

Binära data ingår ofta i XML med base64-kodning. När du strimlade den här XML-koden med hjälp av OPENXML får du base64-kodade data. Det här exemplet visar hur du kan konvertera base64-kodade data tillbaka till binärt.

Skapa en tabell med binära exempeldata.
Använd en FOR XML-fråga och alternativet BINARY BASE64 för att konstruera XML som har binära data kodade som base64.
Använd OPENXML för att splittra XML-filen. Data som returneras av OPENXML är base64-kodade data. Anropa funktionen .value för att konvertera den tillbaka till binär.

CREATE TABLE T (Col1 int primary key, Col2 varbinary(100));
GO
-- Insert sample binary data
INSERT T VALUES(1, 0x1234567890);
GO
-- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)
SELECT * FROM T
FOR XML AUTO, BINARY BASE64;
GO
-- result
-- <T Col1="1" Col2="EjRWeJA="/>

-- Now shredd the sample XML using OPENXML.
-- Call the .value function to convert
-- the base64 encoded data returned by OPENXML to binary.
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT, '<T Col1="1" Col2="EjRWeJA="/>';
SELECT Col1,
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol
FROM openxml(@h, '/T')
WITH (Col1 integer, Col2 varchar(max)) ;
EXEC sp_xml_removedocument @h;
GO

Det här är resultatet. De binära data som returneras är de ursprungliga binära data i tabell T.

Col1        BinaryCol
----------- ---------------------
1           0x1234567890

Se även

Feedback

Var den här sidan hjälpsam?

Last updated on 2025-11-18

Dela via

Exempel: Använda OPENXML

Examples

A. Köra en SELECT-instruktion med OPENXML

B. Ange ColPattern för mappning mellan raderuppsättningskolumner och XML-attribut och -element

C. Kombinera attributcentrerad och elementcentrerad mappning

D. Ange XPath-funktionen text() som ColPattern

E. Ange TableName i WITH-satsen

F. Hämta resultatet i ett kanttabellformat

G. Ange radmönster som slutar med ett attribut

H. Ange ett XML-dokument som har flera textnoder

I. Ange XML-datatypen i WITH-satsen

J. Hämta enskilda värden från flervärdesattribut

K. Hämta binära data från base64-kodade data i XML

Se även

Feedback

Ytterligare resurser