openxml (Transact-sql)
openxml, xml belgesinin üzerinde satır kümesi görüntüleme sağlar. openxml satır kümesi sağlayıcı olduğundan, openxml kullanılabilir Transact-SQLhangi satır kümesi içinde bir tablo, görünüm veya openrowset işlevini gibi sağlayıcılar-ebilmek gözükmek deyimleri.
.gif "Konu bağlantısı simgesi")
Transact-SQL Sözdizim Kuralları
Sözdizimi
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
Bağımsız değişkenler
idoc
xml belgesinin iç gösterimi belge tanıtıcı iş. xml belgesinin iç gösterimi çağrılarak oluşturulan sp_xml_preparedocument.rowpattern
Düğümleri tanımlamak için kullanılan XPath model (kimin ele geçirilir xml belgesindeki idocparametresi) satır işlenecek.flags
xml verileri ve ilişkili satır kümesi ve dökülmeye üzerinde sütun nasıl doldurulmalıdır arasında kullanılacak eşleme gösterir. flagsİsteğe bağlı bir giriş parametresidir ve aşağıdaki değerlerden biri olabilir.Bayt değeri
Açıklama
0
Varsayılan olarak özniteliği merkezli eşleme.
1
Kullanım özniteliği merkezli eşleme. xml_elements ile kombine edilebilir. Bu durumda, özniteliği merkezli eşleme ilk olarak uygulanır ve sonra öğesi merkezli eşleme henüz ile ele değil tüm sütunlar için uygulanır.
2
Kullanım öğesi merkezli eşleme. XML_ATTRIBUTES ile kombine edilebilir. Bu durumda, özniteliği merkezli eşleme ilk olarak uygulanır ve sonra öğesi merkezli eşleme için tüm sütunları henüz ele uygulanır.
8
-Ebilmek var olmak kombine (mantıksal or) XML_ATTRIBUTES ya da xml_elements. Tüketilen veri taşma özelliği kopyalanacağı değil alma bağlamında, bu bayrak gösterir @ mp: xmltext.
SchemaDeclaration
Formun şema tanımı: ColName ColType[ColPattern | MetaProperty] [, ColNameColType [ColPattern | MetaProperty]...]ColName
Satır kümesi sütun addır.ColType
İse SQL Serversatır kümesi sütun veri türü. Sütun türlerini temel den farklıysa xmlveri türü özniteliği, tip zorlama gerçekleşir.ColPattern
Nasıl xml düğümleri sütunları eşlenmiş olması gereken açıklayan bir isteğe bağlı, genel XPath kalıptır. Eğer ColPatternbelirtilen, varsayılan eşleme (özniteliği merkezli veya öğesi merkezli tarafından belirtilen eşleme flags) yer alır.Belirtilen XPath desen ColPatterneşleme özel niteliğine belirtmek için kullanılır (durumunda özniteliği merkezli ve öğesi merkezli eşleme) üzerine yazar veya tarafından gösterilen varsayılan eşleme geliştirir flags.
Olarak belirtilen genel XPath deseni ColPatternda metaproperties çekmek.
MetaProperty
Metaproperties openxml tarafından sağlanan biridir. Eğer MetaPropertybelirtilirse, sütunu içeren metaproperty tarafından sağlanan bilgi. xml düğümleri hakkında bilgileri (örneğin, göreli konumu ve ad bilgileri) ayıklamak metaproperties sağlar. Bu, metinsel gösterimi görünür olandan daha fazla bilgi sağlar.
TableName
Tablo adı da verilebilir (yerine SchemaDeclaration) zaten istenen şema içeren bir tablo var ve hiçbir sütun desenleri gereklidir.
Açıklamalar
WITH yan tümcesi kullanarak ya da bir satır kümesi biçiminde (ve ek eşleştirme bilgileri gerektiği gibi) sağlayan SchemaDeclarationya da varolan bir belirtme TableName. İsteğe bağlı yan tümcesi ile belirtilmezse, sonuçlar döndürülür bir kenar tablo biçiminde. Kenar Tablo ayrıntılı xml belge yapısı (örneğin, öğe/öznitelik adları, belge hiyerarşisi, ad, PIs ve oğlu) tek bir tablo gösterir.
Aşağıdaki tablo yapısını tanımlayan kenar tablosu.
Sütun adı |
Veri türü |
Açıklama |
|---|---|---|
ID |
bigint |
Belge düğümü benzersiz kimliğidir. Kök öğe, Kımlık değeri 0 vardır. Negatif Kimliği değerleri aittir. |
ParentID |
bigint |
Düğümün üst tanımlar. Bu kimliği tarafından belirtilen üst mutlaka üst öğe değil, ama olan üst bu kimliği tarafından tanımlanan düğüm NodeType bağlıdır Örneğin, düğüm bir metin düğümü ise, o üst öznitelik düğümü olabilir. xml belgesinde seviyesi üst düğümse, ParentID null olur. |
NodeType |
int |
Düğüm türü tanımlar. xml dom düğüm türüne karşılık gelen tamsayı numaralandırma. Düğüm türleri şunlardır: 1 = Eleman düğümü 2 = Öznitelik düğümü 3 = Metin düğümü |
İsim |
nvarchar |
Öğe ya da özniteliğin yerel adını verir. 'S dom nesnesi bir ad yoksa null. |
önek |
nvarchar |
Düğüm adı ad alanı öneki var. |
uri |
nvarchar |
Ad alanı URI düğüm. Ad değeri boş ise, yok. |
veri türü |
nvarchar |
Gerçek veri türü öğe veya öznitelik satır, aksi takdirde null. Veri türü satır içi dtd veya satır içi şema olayla. |
prev |
bigint |
Önceki eşdüzey öğesi xml kimliğidir. Olan hiçbir doğrudan önceki eşdüzey ise null. |
metin |
ntext |
Öznitelik değerini veya metin biçiminde öğe içeriği içerir (veya null ise kenar tablosu girdisi bir değer gerektirmez). |
Örnekler
A.Bir select deyimi openxml ile kullanma
Aşağıdaki örnek xml resim dahili bir gösterimini kullanarak oluşturur sp_xml_preparedocument. A SELECTkullanan deyimi bir OPENXMLsatır kümesi sağlayıcı daha sonra karşı xml belgesinin iç temsili idam.
flagDeğerini ayarlamak 1. Bu gösterir özniteliği merkezli eşleme. Bu nedenle, xml öznitelikleri satır kümesi sütunlarda eşleştirin. rowpatternBelirtildiği gibi /ROOT/Customertanımlayan <Customers>işlenecek düğümleri.
İsteğe bağlı ColPattern(sütun deseni) parametresi belirtilen sütun adı xml öznitelik adları eşleştiğinden.
OPENXMLSatır kümesi sağlayıcı oluşturur iki sütun satır kümesi ( CustomerID ve ContactName) hangi SELECTdeyimi alır gerekli sütunları (Bu durumda, tüm sütunlar).
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<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 Gonzlez">
<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 @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<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 Gonzlez">
<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 @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
Sonuç kümesi buradadır.
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
Eğer aynı SELECTdeyimi ile yürütülür flagsiçin 2, gösteren öğesi merkezli eşleme, değerleri CustomerIDve ContactNameiçin her ikisi de xml belgesindeki müşterilerin boş olarak, çünkü döndürülen <Customers>öğeleri herhangi bir alt öğeleri yoktur.
Sonuç kümesi buradadır.
CustomerID ContactName
---------- -----------
NULL NULL
NULL NULL
CustomerID ContactName
---------- -----------
NULL NULL
NULL NULL
B.Sütunları ve xml öznitelikleri arasında eşleştirme ColPattern belirtme
Aşağıdaki sorgu xml belgesinden müşteri kimliği, sipariş tarihi, ürün kimliği ve Miktar öznitelikleri döndürür. rowpatternTanımlayan <OrderDetails>elemanları. ProductIDve Quantityöznitelikleri olan <OrderDetails>öğesi. Ancak, OrderID, CustomerID, ve OrderDateüst öğe öznitelikleri (<Orders>).
İsteğe bağlı ColPattern belirtilir. Bu, aşağıdakileri gösterir:
OrderID, CustomerID, Ve OrderDatetarafından tanımlanan düğüm üst öznitelikleri satır kümesi Eşle rowpatternxml belgesindeki.
ProdIDSatır kümesi sütun eşlemeleri için ProductIDözniteliği ve Qtysatır kümesi sütun eşlemeleri için Quantityözniteliği, belirtilen düğüm rowpattern.
Her ne kadar öğesi merkezli eşleme tarafından belirtilen flagsparametresi, belirtilen eşleme ColPatternBu eşleştirme üzerine yazar.
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<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 Gonzlez">
<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 @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<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 Gonzlez">
<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 @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
Sonuç kümesi buradadır.
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
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
C.Kenar tablo biçiminde sonuçlar elde etme
Örnek xml belgesi aşağıdaki örnekte oluşur <Customers>, <Orders>, ve <Order_0020_Details>elemanları. İlk, sp_xml_preparedocument bir belge tanıtıcı elde etmek denir. Bu belge tutamacı geçer OPENXML.
De OPENXMLdeyimi, rowpattern(/ROOT/Customers) tanımlayan <Customers>işlem düğümler. WITH yan tümcesi verilmez çünkü OPENXMLsatır kümesi döndüren bir kenar tablo biçiminde.
Son olarak SELECTdeyimi alır tüm sütunları kenar tablosu.
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc