Jegyzet
Az oldalhoz való hozzáférés engedélyezést igényel. Próbálhatod be jelentkezni vagy könyvtárat váltani.
Az oldalhoz való hozzáférés engedélyezést igényel. Megpróbálhatod a könyvtár váltását.
A következőkre vonatkozik:
SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példánySQL-adatbázis a Microsoft Fabricben
A cikkben szereplő példák azt mutatják, hogyan használható az OPENXML egy XML-dokumentum sorhalmaznézetének létrehozására. Az OPENXML szintaxisával kapcsolatos információkért lásd: OPENXML (Transact-SQL). A példák az OPENXML minden aspektusát szemléltetik, de az OPENXML-ben ne adjon meg metatulajdonságokat. A metaproperties OPENXML-ben való megadásáról további információt a Metaproperties megadása az OPENXML-ben című témakörben talál.
Példák
Az adatok beolvasásakor a sorpattern a sorokat meghatározó XML-dokumentumban található csomópontok azonosítására szolgál. Emellett a sorpattern az MSXML XPath implementációban használt XPath-mintanyelvben van kifejezve. Ha például a minta egy elemben vagy attribútumban végződik, a sor minden olyan elemhez vagy attribútumcsomóponthoz létrejön, amelyet a sorpattern választ ki.
A jelzők értéke alapértelmezett leképezést biztosít. Ha a SchemaDeclaration nem ad meg ColPattern értéket, a rendszer feltételezi a jelölőkben megadott leképezést. A rendszer figyelmen kívül hagyja a jelzők értékét, ha a ColPattern a SchemaDeclaration függvényben van megadva. A megadott ColPattern határozza meg a leképezést, az attribútumcentrikus vagy az elemközpontú, valamint a túlcsordulás és a nem feldolgozott adatok kezelésének viselkedését.
A. SELECT utasítás végrehajtása OPENXML-lel
A példában szereplő XML-dokumentum a <Customer>, <Order>és <OrderDetail> az elemekből áll. Az OPENXML utasítás egy kétoszlopos sorkészletben( CustomerID és ContactName) kéri le az ügyféladatokat az XML-dokumentumból.
Először a sp_xml_preparedocument tárolt eljárást kell meghívni egy dokumentumleíró beszerzéséhez. Ezt a dokumentumleírót adjuk át az OPENXML-nek.
Az OPENXML utasítás a következőket szemlélteti:
A rowpattern (/ROOT/Customer) azonosítja a
<Customer>csomópontokat feldolgozásra.A jelzők paraméterértéke 1 , és attribútumcentrikus leképezést jelez. Ennek eredményeképpen az XML-attribútumok a SchemaDeclarationban definiált sorhalmaz oszlopaihoz lesznek megfeleltetve.
A SchemaDeclaration with záradékában a megadott ColName értékek megegyeznek a megfelelő XML-attribútumnevekkel. Ezért a ColPattern paraméter nincs megadva a SchemaDeclarationban.
A SELECT utasítás ezután lekéri az OPENXML által biztosított sorhalmaz összes oszlopát.
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;
Ez az eredmény:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
Mivel az <Customer> elemek nem rendelkeznek alelemekkel, ha ugyanazt a SELECT utasítást az elemközpontú leképezést jelző 2-esjelölőkkel hajtja végre, akkor a CustomerID és a ContactName értékeket a rendszer null értékként adja vissza mindkét ügyfél esetében.
A @xmlDocument xml vagy(n)varchar(max) típusú is lehet.
Ha a <CustomerID> és <ContactName> elemek alelemek az XML-dokumentumban, az elemközpontú leképezés lekéri az értékeket.
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;
Ez az eredmény:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
A sp_xml_preparedocument által visszaadott dokumentumleíró a köteg alatt érvényes, nem pedig a munkamenetben.
B. A ColPattern megadása a sorkészletoszlopok és az XML-attribútumok és -elemek közötti leképezéshez
Ez a példa bemutatja, hogyan van megadva az XPath-minta az opcionális ColPattern paraméterben, hogy megfeleltetést biztosítson a sorhalmazoszlopok és az XML-attribútumok és -elemek között.
A példában szereplő XML-dokumentum a <Customer>, <Order>és <OrderDetail> az elemekből áll. Az OPENXML utasítás sorkészletként (CustomerID, OrderDate, ProdID és Qty) kéri le az ügyfél- és rendelésadatokat az XML-dokumentumból.
Először a sp_xml_preparedocument tárolt eljárást kell meghívni egy dokumentumleíró beszerzéséhez. Ezt a dokumentumleírót adjuk át az OPENXML-nek.
Az OPENXML utasítás a következőket szemlélteti:
-
A “rowpattern” (/ROOT/Customer/Order/OrderDetail) azonosítja a
<OrderDetail>feldolgozandó csomópontokat.
Illusztrációként a jelző paraméter értéke 2 , és elemközpontú leképezést jelez. A ColPatternben megadott leképezés azonban felülírja ezt a leképezést. Ez azt jelzi, hogy a ColPatternben megadott XPath-minta attribútumokhoz rendeli a sorhalmaz oszlopait. Ez attribútumcentrikus leképezést eredményez.
A SchemaDeclarationban a WITH záradékban a ColPattern is meg van adva a ColName és a ColType paraméterekkel. Az opcionális ColPattern a megadott XPath-minta, és a következőket jelzi:
A sorkészlet OrderID, CustomerID és OrderDate oszlopai a rowpattern által azonosított csomópontok szülőjének attribútumaihoz, a rowpattern pedig a
<OrderDetail>csomópontokat azonosítják. Ezért a CustomerID és az OrderDate oszlopok az elem CustomerID és OrderDate attribútumaihoz lesznek megfeleltetve<Order>.A sorkészlet ProdID és Qty oszlopai a sorpatternben azonosított csomópontok ProductID és Quantity attribútumaihoz lesznek megfeleltetve.
A SELECT utasítás ezután lekéri az OPENXML által biztosított sorhalmaz összes oszlopát.
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;
Ez az eredmény:
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
A ColPatternként megadott XPath-minta is megadható, hogy az XML-elemeket a sorhalmaz oszlopaihoz rendelje. Ez elemközpontú leképezést eredményez. Az alábbi példában az XML-dokumentum <CustomerID> és <OrderDate> az <Orders> elem alelemei. Mivel a ColPattern felülírja a jelölők paraméterében megadott leképezést , a jelölők paramétere nincs megadva az OPENXML-ben.
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. Attribútum-központú és elemközpontú leképezés kombinálása
Ebben a példában a jelölőparaméter3-ra van állítva, és azt jelzi, hogy az attribútum-központú és az elemközpontú leképezés is alkalmazva lesz. Ebben az esetben először az attribútumcentrikus leképezést alkalmazza a rendszer, majd az elemközpontú leképezést alkalmazza a rendszer az összes olyan oszlopra, amely még nem foglalkozik vele.
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;
Ez az eredmény
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
Az attribútum-központú leképezés a CustomerID-hez lesz alkalmazva. Az elemben nincs ContactName attribútum <Customer> . Ezért az elemközpontú leképezést alkalmazza a rendszer.
D. Adja meg a text() XPath függvényt ColPatternként
A példában szereplő XML-dokumentum a <Customer> és a <Order> elemekből áll. Az OPENXML utasítás lekér egy táblázatkészletet, amely a elem <Order> attribútumából, a rowpattern által azonosított csomópont szülői azonosítójából, és az elemtartalom levél-érték sztringjéből áll.
Először a sp_xml_preparedocument tárolt eljárást kell meghívni egy dokumentumleíró beszerzéséhez. Ezt a dokumentumleírót adjuk át az OPENXML-nek.
Az OPENXML utasítás a következőket szemlélteti:
A rowpattern (/root/Customer/Order) azonosítja a feldolgozandó
<Order>csomópontokat.A jelzők paraméterértéke 1 , és attribútumcentrikus leképezést jelez. Ennek eredményeképpen az XML-attribútumok megfeleltetik a SchemaDeclarationban definiált sorhalmazoszlopokat.
A WITH záradék SchemaDeclaration elemében az oid és az összeg sorhalmaz oszlopnevei megegyeznek a megfelelő XML-attribútumnevekkel. Ezért a ColPattern paraméter nincs megadva. A sorhalmaz megjegyzés oszlopában az XPath függvény
text()ColPatternként van megadva. Ez felülírja a jelölőkben megadott attribútumcentrikus leképezést, és az oszlop tartalmazza az elemtartalom levél-érték sztringét.
A SELECT utasítás ezután lekéri az OPENXML által biztosított sorhalmaz összes oszlopát.
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;
Ez az eredmény:
oid amount comment
----- ----------- -----------------------------
O1 3.5 NULL
O2 13.4 Customer was very satisfied
O3 100.0 Happy Customer.
O4 10000.0 NULL
E. Adja meg a TableName értéket a WITH záradékban
Ez a példa a SchemaDeclaration helyett a WITH záradékBan adja meg a TableName nevet. Ez akkor hasznos, ha olyan táblával rendelkezik, amely rendelkezik a kívánt struktúrával, és nincs szükség oszlopmintákra( ColPattern paraméter).
A példában szereplő XML-dokumentum a <Customer> és a <Order> elemekből áll. Az OPENXML utasítás egy háromoszlopos sorkészletben (oid, date és amount) kéri le a rendelési adatokat az XML-dokumentumból.
Először a sp_xml_preparedocument tárolt eljárást kell meghívni egy dokumentumleíró beszerzéséhez. Ezt a dokumentumleírót adjuk át az OPENXML-nek.
Az OPENXML utasítás a következőket szemlélteti:
A rowpattern (/root/Customer/Order) azonosítja a feldolgozandó
<Order>csomópontokat.A WITH záradékban nincs SchemaDeclaration. Ehelyett egy táblanév van megadva. Ezért a táblaséma szolgál a sorkészlet-sémaként.
A jelzők paraméterértéke 1 , és attribútumcentrikus leképezést jelez. Ezért a rowpattern által azonosított elemek attribútumai az azonos nevű sorhalmazoszlopokra lesznek leképezve.
A SELECT utasítás ezután lekéri az OPENXML által biztosított sorhalmaz összes oszlopát.
-- 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;
Ez az eredmény:
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. Az eredmény éltábla formátumban való lekérése
Ebben a példában a WITH záradék nincs megadva az OPENXML utasításban. Ennek eredményeként az OPENXML által létrehozott sorhalmaz éltáblaformátummal rendelkezik. A SELECT utasítás a peremtábla összes oszlopát adja vissza.
A példában szereplő XML-mintadokumentum a <Customer>, <Order>és <OrderDetail> az elemekből áll.
Először a sp_xml_preparedocument tárolt eljárást kell meghívni egy dokumentumleíró beszerzéséhez. Ezt a dokumentumleírót adjuk át az OPENXML-nek.
Az OPENXML utasítás a következőket szemlélteti:
A rowpattern (/ROOT/Customer) azonosítja a
<Customer>csomópontokat feldolgozásra.A WITH záradék nincs megadva. Ezért az OPENXML a sorkészletet éltábla formátumban adja vissza.
A SELECT utasítás ezután lekéri a peremtábla összes oszlopát.
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;
Az eredményt éltáblaként adjuk vissza. Az adatok lekéréséhez lekérdezéseket írhat a peremtáblára. Például:
Az alábbi lekérdezés a dokumentum ügyfélcsomópontjainak számát adja vissza. Mivel a WITH záradék nincs megadva, az OPENXML egy éltáblát ad vissza. A SELECT utasítás lekérdezi az éltáblát.
SELECT count(*) FROM OPENXML(@docHandle, '/') WHERE localname = 'Customer';Az alábbi lekérdezés az elemtípusú XML-csomópontok helyi nevét adja vissza.
SELECT distinct localname FROM OPENXML(@docHandle, '/') WHERE nodetype = 1 ORDER BY localname;
G. Attribútummal végződő sorpattern megadása
A példában szereplő XML-dokumentum a <Customer>, <Order>és <OrderDetail> az elemekből áll. Az OPENXML utasítás egy háromoszlopos sorkészlet (ProductID, Quantity és OrderID) rendelési adataival kapcsolatos információkat kér le az XML-dokumentumból.
Először a sp_xml_preparedocument egy dokumentumleíró beszerzéséhez hívjuk meg. Ezt a dokumentumleírót adjuk át az OPENXML-nek.
Az OPENXML utasítás a következőket szemlélteti:
A rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) egy ProductID XML-attribútummal végződik. Az eredményként kapott sorhalmazban egy sor jön létre az XML-dokumentumban kijelölt minden attribútumcsomóponthoz.
Ebben a példában a jelölőparaméter nincs megadva. Ehelyett a leképezéseket a ColPattern paraméter adja meg.
A WITH záradék SchemaDeclaration elemében a ColPattern a ColName és a ColType paraméterekkel is meg van adva. Az opcionális ColPattern az XPath-minta, amely a következőket jelzi:
A sorhalmaz ProdID oszlopához ColPatternként megadott XPath-minta (.) azonosítja a környezeti csomópontot, az aktuális csomópontot. A megadott sorpattern szerint ez az elem
<OrderDetail>attribútuma.ColPattern, .. /@Mennyiség, amelyet a sorokhoz rendelt Qty oszlop specifikál, meghatározza a szülő csomópont , a termékazonosító
<OrderDetail>környezetéhez tartozó<OrderDetail>attribútumát.Hasonlóképpen, a ColPattern, a ../../@OrderID, amely az OID oszlop számára van megadva a sorhalmazban, azonosítja a környezeti csomópont szülőcsomópontjának OrderID attribútumát. A szülőcsomópont az
<OrderDetail>, és a környezet csomópont<ProductID>.
A SELECT utasítás ezután lekéri az OPENXML által biztosított sorhalmaz összes oszlopát.
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;
Ez az eredmény:
ProdID Qty OID
----------- ----------- -------
11 12 10248
42 10 10248
72 3 10283
H. Több szöveges csomópontot tartalmazó XML-dokumentum megadása
Ha egy XML-dokumentumban több szövegcsomópont is található, a ColPatternneltext() rendelkező SELECT utasítás az összes helyett csak az első szöveges csomópontot adja vissza. Például:
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;
A SELECT utasítás a T értéket adja eredményül, és nem a TaU-t.
I. Adja meg az XML-adattípust a WITH záradékban
A WITH záradékban az XML-adattípus oszlopára leképezett oszlopmintának – akár begépelt, akár nem beírt – üres sorozatot vagy elemek sorozatát, feldolgozási utasításokat, szövegcsomópontokat és megjegyzéseket kell visszaadnia. Az adatok xml-adattípusba kerülnek.
Az alábbi példában a WITH záradék táblaséma-deklarációja xml típusú oszlopokat tartalmaz.
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;
Pontosabban egy xml-típusváltozót (@x) ad át a sp_xml_preparedocument() függvénynek.
Ez az eredmény:
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>
Jegyezze fel a következőket az eredményből:
A varchar(30) típusú lname oszlop értéke a megfelelő
<lname>elemből lesz lekérve.Az xml típusú xmlname oszlop esetében a rendszer ugyanazt a névelemet adja vissza, mint az értékét.
A zászló értéke 10. A 10 2+ 8-at jelent, ahol a 2 elemközpontú leképezést, a 8 pedig azt jelzi, hogy a WITH záradékban definiált OverFlow oszlophoz csak a nem megadott XML-adatokat szabad hozzáadni. Ha a jelzőt 2 értékre állítja, a rendszer a teljes XML-dokumentumot a WITH záradékban megadott OverFlow oszlopba másolja.
Ha a WITH záradék oszlopa egy beírt XML-oszlop, és az XML-példány nem erősíti meg a sémát, a rendszer hibát ad vissza.
J. Egyedi értékek lekérése többértékű attribútumokból
Az XML-dokumentumok attribútumai többértékűek lehetnek. Az IDREFS attribútum például többértékű lehet. Egy XML-dokumentumban a többértékű attribútumértékek sztringként vannak megadva, az értékeket szóköz választja el egymástól. A következő XML-dokumentumban a Student elem < attribútuma és az > attribútuma többértékű. Az egyes értékek többértékű XML-attribútumból való beolvasása és az egyes értékek külön sorban való tárolása az adatbázisban további munkát igényel. Ez a példa a folyamatot mutatja be.
Ez a minta XML-dokumentum a következő elemekből áll:
<Diák>
Az azonosító (tanulóazonosító), a név és a résztvevők attribútumai. A Attends attribútum egy többértékű attribútum.
<Osztály>
Az azonosító (osztályazonosító), a név és az attendedBy attribútumok. Az attendedBy attribútum egy többértékű attribútum.
A Student<>attribútuma és az osztályban<> lévő AttendBy attribútum m:n kapcsolatot jelöl a Student és az Class táblák között. Egy tanuló sok osztályt vehet igénybe, egy osztálynak pedig sok tanulója lehet.
Tegyük fel, hogy a dokumentumot a következőképpen szeretné darabolni és menteni az adatbázisban:
Mentse az
<Student>adatokat a Diákok táblában.Mentse az
<Class>adatokat a Tanfolyamok táblában.Mentse az m:n kapcsolat adatait a Student és az Class között a CourseAttendence táblában. Az értékek kinyeréséhez további munka szükséges. Az információk lekéréséhez és a táblában való tárolásához használja az alábbi tárolt eljárásokat:
Insert_Idrefs_Values
Beszúrja a kurzusazonosító és a tanulóazonosító értékeit a CourseAttendence táblába.
Extract_idrefs_values
Kinyeri az egyes diákazonosítókat az egyes <kurzuselemekből> . A rendszer egy éltáblát használ ezeknek az értékeknek a lekéréséhez.
A lépések a következők:
-- 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. Bináris lekérése base64 kódolású adatokból XML-ben
A bináris adatok gyakran szerepelnek az XML-ben base64 kódolással. Amikor ezt az XML-t az OPENXML használatával aprítja, megkapja a base64 kódolású adatokat. Ez a példa bemutatja, hogyan konvertálhatja a base64 kódolású adatokat binárissá.
Hozzon létre egy táblát bináris mintaadatokkal.
Az XML-lekérdezés és a BINÁRIS BASE64 beállítás használatával olyan XML-t hozhat létre, amely a bináris adatokat base64-ként kódolja.
Az XML-t az OPENXML használatával aprítsa fel. Az OPENXML által visszaadott adatok base64 kódolású adatok lesznek. Ezután hívja meg a függvényt
.value, hogy visszakonvertálja binárissá.
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
Ez az eredmény. A visszaadott bináris adatok a T tábla eredeti bináris adatai.
Col1 BinaryCol
----------- ---------------------
1 0x1234567890