OPENXML kullanma
Bu konudaki örneklerde OPENXML bir XML belgesinin satır kümesi kümesi görünümü oluşturmak için nasıl kullanıldığını gösterir.OPENXML sözdizimi hakkında bilgi için bkz: OPENXML (Transact-SQL). Örnekler OPENXML tüm yönlerini gösterir, ancak metaproperties OPENXML belirtin.OPENXML içinde metaproperties belirtme hakkında daha fazla bilgi için bkz: OPENXML içinde Metaproperties belirtme.
Örnekler
Verileri alınıyor rowpattern XML belgesindeki satırları belirlemek düğümleri tanımlamak için kullanılır.Ayrıca, rowpattern MSXML XPath uygulamasında kullanılan XPath desen dilde gösterilir.Bir öğe veya öznitelik deseni sona ererse, örneğin, bir satır olarak işaretli her öğe veya öznitelik düğümü oluşturulur rowpattern.
The flags value provides default mapping.Yüklenmemişse ColPattern belirtilirSchemaDeclaration içinde belirtilen eşlemeBayraklar kabul edilir.The flags value is ignored if ColPattern is specified in SchemaDeclaration.Belirtilen ColPattern merkezli öznitelik veya öğe merkezli, eşleme ve ayrıca taşma ve unconsumed veri ilgili davranış belirler.
C.OPENXML ile basit bir deyim yürütme
Bu örnek XML belgesinde oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri. OPENXML deyim iki sütunlu satır kümesi müşteri bilgilerini alır. MüşteriNo and Kişiadı, XML belgesindeki.
Ilk olarak, sp_xml_preparedocument depolanmış yordam, belge bir tanıtıcı elde etmek için denir.OPENXML için bu belgeyi tanıtıcı geçirildi.
OPENXML deyim aşağıda gösterilmiştir:
rowpattern (/ ROOT/müşteri) <Customer> düğümlerin işlemi tanımlar.
The flags parameter value is küme to 1 and indicates öznitelik-centric mapping.Sonuç olarak, XML özniteliği tanımlanmış satır kümesi içindeki sütunları eşleştirmek SchemaDeclaration.
Içinde SchemaDeclaration, belirtilen WITH yan tümcesindekiColName değerleri, ilgili XML özniteliği adı aynı.Bu nedenle, ColPattern parametre belirtilmediSchemaDeclaration.
deyim tüm sütunlara OPENXML tarafından sağlanan satır kümesi kümesi yer alır.
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 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 @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
Bu sonucu oluşur:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
Çünkü <Customer> aynı deyim ile yürütülen öğeleri herhangi bir alt yoksa Bayraklar küme to 2 değerlerini öğe merkezli eşleme belirtmek içinMüşteriNo and Kişiadı her ikisi için de müşterilerin null olarak döndürülür.
@ XmlDocument de olabilir XML türü veya (n)varchar(max) türü.
If <CustomerID> ve <ContactName> XML belgesinde, alt, öğe merkezli eşleme değerleri alır.
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 Gonzlez</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
Bu sonucu oluşur:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
Belge tanıtıcısı tarafından döndürülen unutmayın. sp_xml_preparedocument toplu iş ve değil oturum süresince geçerli olur.
b.satır kümesi kümesi, sütun ve XML öznitelikleri öğeler arasında eşleme ColPattern belirtme
Bu örnek, nasıl XPath desen isteğe bağlı belirtilen gösterir. ColPattern parametre satır kümesi kümesi, sütun, XML öznitelikleri ve öğeleri eşleştirmesini sağlar.
Bu örnek XML belgesinde oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri. OPENXML deyim müşteri ve sipariş bilgilerini satır kümesi kümesi () olarak alır.MüşteriNo, OrderDate, ProdID, and Mkt.) XML belgesinden.
Ilk olarak, sp_xml_preparedocument depolanmış yordam, belge bir tanıtıcı elde etmek için denir.OPENXML için bu belgeyi tanıtıcı geçirildi.
OPENXML deyim aşağıda gösterilmiştir:
- rowpattern (/ ROOT/Müşteri/sipariş/OrderDetail) <OrderDetail> düğümlerin işlemi tanımlar.
Resimde için Bayraklar parametre değeri için ayarlanmıştır2 ve öğe merkezli eşlemesini gösterir.Ancak, eşleme belirtilen ColPattern , bu eşleştirme üzerine yazar.Diğer bir deyişle, XPath desen belirtildi... ColPattern satır kümesi kümesi sütun özniteliklerini eşleştirir.Bu öznitelik merkezli eşlemesindeki olur.
Içinde SchemaDeclaration, in the WITH yan tümce, ColPattern ile de belirtilenColName and ColType parametreleri.Isteğe bağlı ColPattern belirtilen XPath desen ve aşağıdakileri gösterir:
The OrderID, CustomerID, and OrderDate columns in the satır kümesi map to the attributes of the parent of the nodes identified by rowpattern, and rowpattern identifies the <OrderDetail> nodes.Bu nedenle, MüşteriNo and OrderDate sütunları eşleştirmekMüşteriNo and OrderDate <Order> öğenin özniteliklerini.
The ProdID and Qty columns in the satır kümesi map to the ProductID and Quantity attributes of the nodes identified in rowpattern.
deyim tüm sütunlara OPENXML tarafından sağlanan satır kümesi kümesi yer alır.
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 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 @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
Bu sonucu oluşur:
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
Belirtilen XPath deseni ColPattern , satır kümesi sütunlara XML öğeleri eşlemek için belirtilebilir.Bu öğe merkezli eşlemesindeki olur.Aşağıdaki örnekte, XML belgesi <CustomerID> ve <OrderDate> alt öğeleri olan olan <Orders> Öğe. Çünkü ColPattern içinde belirtilen eşleme üzerine yazar.Bayraklar parametresiBayraklar parametresi OPENXML belirtilmedi.
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 Gonzlez">
<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.Birleştirme öznitelik-merkezli ve öğe merkezli bir eşleme
Bu örnekte, Bayraklar için parametresi küme3 ve öznitelik merkezli ve öğe merkezli eşleme uygulanacağını gösterir.Bu durumda, öznitelik merkezli eşleme önce uygulanır ve ardından öğe merkezli eşleme ile henüz önceleri tüm sütunlar için uygulanır.
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 Gonzlez</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
Sonuç budur
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
Öznitelik merkezli eşleme için uygulanır MüşteriNo.Yok yok Kişiadı öznitelik <Customer> öğesinde.Bu nedenle, öğe merkezli eşleme uygulanır.
d.XPath işlev text() ColPattern belirtme
Bu örnek XML belgesinde oluşur <Customer> ve <Order> öğeleri. OPENXML deyim oluşur satır kümesi alır OID öğenin, üst tarafından tanımlanan düğüm KIMLIĞI <Order> özniteliğindenrowpatternve yaprak değeri dize öğenin içeriği.
Ilk olarak, sp_xml_preparedocument depolanmış yordam, belge bir tanıtıcı elde etmek için denir.OPENXML için bu belgeyi tanıtıcı geçirildi.
OPENXML deyim aşağıda gösterilmiştir:
rowpattern (/ kök/Müşteri/sipariş) <Order> düğümlerin işlemi tanımlar.
The flags parameter value is küme to 1 and indicates öznitelik-centric mapping.Sonuç olarak, XML özniteliği tanımlanmış satır kümesi kümesi kümesine sütun eşleme SchemaDeclaration.
Içinde SchemaDeclaration WITH yan tümce, OID and tutarı satır kümesi kümesi, sütun adları, ilgili XML özniteliği adı eşleşmiyor.Bu nedenle, ColPattern parametresi belirtilmemiş.Için Açıklama satır kümesi, XPath işlev, sütunText() belirtilirColPattern.Bu, belirtilen öznitelik merkezli bir eşleme üzerine yazar Bayraklarve sütun öğesinin içeriği yaprak değer dizesi içerir.
deyim tüm sütunlara OPENXML tarafından sağlanan satır kümesi kümesi yer alır.
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
Bu sonucu oluşur:
oid amount comment
----- ----------- -----------------------------
O1 3.5 NULL
O2 13.4 Customer was very satisfied
O3 100.0 Happy Customer.
O4 10000.0 NULL
e.TableName WITH belirtme yan tümce
Bu örnek belirtir. TableName yerine WITH yan tümcesindeSchemaDeclaration.Hiçbir sütun desenleri ve istediğiniz yapısına sahip bir tablo varsa yararlıdır ColPattern parametresi gereklidir.
Bu örnek XML belgesinde oluşur <Customer> ve <Order> öğeleri. OPENXML deyim üç sütun satır kümesi kümesi () bir sipariş bilgilerini alır.OID, TARİH, and tutarı) XML belgesinden.
Ilk olarak, sp_xml_preparedocument depolanmış yordam, belge bir tanıtıcı elde etmek için denir.OPENXML için bu belgeyi tanıtıcı geçirildi.
OPENXML deyim aşağıda gösterilmiştir:
rowpattern (/ kök/Müşteri/sipariş) <Order> düğümlerin işlemi tanımlar.
Yok yok SchemaDeclaration WITH yan tümce.Bunun yerine, bir tablo adı belirtildi.Bu nedenle, tablo şema satır kümesi kümesi schema olarak kullanılır.
The flags parameter value is küme to 1 and indicates öznitelik-centric mapping.Bu nedenle, tanımlanan öğeleri, öznitelikleri rowpattern, aynı ada sahip satır kümesi kümesi kümesine sütun eşlenir.
deyim tüm sütunlara OPENXML tarafından sağlanan satır kümesi kümesi yer alır.
-- 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
Bu sonucu oluşur:
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.Kenar tablo biçiminde bir sonuç elde etme
Bu örnekte, WITH yan tümce içinde OPENXML belirtilmemiş deyim.Sonuç olarak, OPENXML tarafından oluşturulan satır kümesi kümesi kenar tablo biçimi vardır.deyim kenar tüm sütunlar döndürür tablo.
Örnekte örnek XML belgesi oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri.
Ilk olarak, sp_xml_preparedocument depolanmış yordam, belge bir tanıtıcı elde etmek için denir.OPENXML için bu belgeyi tanıtıcı geçirildi.
OPENXML deyim aşağıda gösterilmiştir:
rowpattern (/ ROOT/müşteri) <Customer> düğümlerin işlemi tanımlar.
WITH yan tümce sağlanır.Bu nedenle, OPENXML kenar tablo biçiminde satır kümesi kümesi döndürür.
SELECT deyiminin ardından, kenarı tüm sütunları alır tablo.
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 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 @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer')
EXEC sp_xml_removedocument @docHandle
Sonuç kenar döndürülür tablo.Kenar tablo bilgilerini almak için sorgular yazabilirsiniz.Örneğin:
Aşağıdaki sorgu sayısını verir... Müşteri düğümlerin belgedeki.WITH yan tümce belirtildiğinden OPENXML bir kenarı tablosu verir.deyim, tablo kenar sorgular.
SELECT count(*) FROM OPENXML(@docHandle, '/') WHERE localname = 'Customer'Aşağıdaki sorguyu yerel öğe türü için XML düğümleri adını döndürür.
SELECT distinct localname FROM OPENXML(@docHandle, '/') WHERE nodetype = 1 ORDER BY localname
g.Bir öznitelik ile biten rowpattern belirtme
Bu örnek XML belgesinde oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri. OPENXML deyim üç sütun satır kümesi kümesi () bir sipariş ayrıntıları hakkında bilgi alır.Productıd, Miktar, and Sipariş Kimliği) XML belgesinden.
Ilk olarak, sp_xml_preparedocument belge bir tanıtıcı elde etmek için verilir.OPENXML için bu belgeyi tanıtıcı geçirildi.
OPENXML deyim aşağıda gösterilmiştir:
rowpattern (/ ROOT/Customer/Order/OrderDetail/@ProductID) bir XML ile biten öznitelik, Productıd.Sonuçta elde edilen satır kümesi XML belgesindeki seçili her öznitelik düğümü için bir satır oluşturulur.
Bu örnekte, Bayraklar parametresi belirtilmemiş.Bunun yerine, eşlemeleri tarafından belirtilen ColPattern parametresi.
Içinde SchemaDeclaration in the WITH yan tümce, ColPattern ile de belirtilenColName ve ColType Parametreler. Isteğe bağlı ColPattern aşağıdakileri belirtmek için belirtilen XPath deseni ise:
XPath deseni (. ) belirtildi.ColPattern for the ProdID satır kümesi kümesi kümesine sütun, içerik düğümü geçerli düğümün tanımlar.Olarak rowpattern olarak belirtildiProductıd <OrderDetail> öğesi özniteliği.
The ColPattern, ../@Quantity, specified for the Qty sütun in the satır kümesi identifies the Quantity öznitelik of the parent, <OrderDetail>, node of the context node, <ProductID>.
Benzer şekilde, ColPattern, ../../@OrderID için belirtilen,oid satır kümesi sütun tanımlarSipariş Kimliği <Order>, üst düğümün içerik düğümünün üst öznitelik.Üst düğüm <OrderDetail>, ve içerik düğümü <ProductID>.
deyim tüm sütunlara OPENXML tarafından sağlanan satır kümesi kümesi yer alır.
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 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 @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
Bu sonucu oluşur:
ProdID Qty OID
----------- ----------- -------
11 12 10248
42 10 10248
72 3 10283
h.Birden fazla metin düğümü olan XML belgesinin belirtme
XML içinde birden çok metin düğüm varsa, belge, bir deyim ile bir ColPattern, Text(), yalnızca ilk metin düğümü yerine tümünü verir.Örneğin:
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
deyim döndürür t sonucu olarak ve TaU.
İ.Veri türü ILE xml belirtme yan tümce
Eşleştirilmiş olan bir sütun deseni WITH yan tümcesindeki XML veri türü sütununu boş bir sıra veya bir dizi işlem yönergeleri, metin düğümlerin ve açıklamalar, öğeleri yazılı veya türlenmemiş, döndürmelidir.Verileri artığını bir XML veri türü.
Aşağıdaki örnekte, ILE tablo şema bildiriminde yan tümce içerir XML türü sütunlarındaki.
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
Özellikle geçirmeden bir XML türü değişkeni (@ x)sp_xml_preparedocument() işlev.
Bu sonucu oluşur:
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>
Sonuç aşağıdakilere dikkat edin:
Için lname sütunvarchar(30) türü değerini, buna karşılık gelen <lname> öğesinden alınır.
Için xmlname sütunXML türünü, aynı adı öğe değeri döndürülür.
Bayrak, 10'a küme.10, 2 + 8 2 Öğesinin merkezli eşleme gösterir ve yalnızca unconsumed XML verilerini WITH içinde tanımlanan taşma sütun için eklenecek 8 gösterir, anlamýna gelir yan tümce.Bayrak, 2 olarak ayarlarsanız, tüm XML belge içinde ILE belirtilen taşma sütuna kopyalanır yan tümce.
WITH yan tümcesindeki sütun yazılan bir XML sütun, örnek XML şemaya Onayla durumunda bir hata döndürülür.
j.Tek tek değerleri çok değerli özniteliklerini alınıyor
Bir XML belgesi çok değerli özniteliklerini olabilir.Örneğin, idrefs öznitelik çok değerli olabilir.Bir XML belgesinde çok değerli öznitelik değerleri boşlukla ayrılmış değerleri içeren bir dize olarak belirtilir.Aşağıdaki XML belgesindeki attends öznitelik < > Öğrenci öğe veattendedBy öznitelik < sınıf > çok değerli. Tek tek değerleri çok değerli bir XML özniteliği alınıyor ve her değer, ayrı bir satırda veritabanında depolamak için ek iş gerektirir.Bu örnek, işlemi gösterir.
Bu örnek XML belgesi aşağıdaki öğelerden oluşur:
<Öğrenci>
The id (student ID), name, and attends attributes.The attends öznitelik is a multivalued öznitelik.
<Sınıfı>
The id (class ID), name, and attendedBy attributes.The attendedBy öznitelik is a multivalued öznitelik.
The attends öznitelik in <Student> and the attendedBy öznitelik in <Class> represent a m:n relationship between the Student and Class tables. Bir öğrenci pek çok sınıf alabilir ve bu sınıf, birçok Öğrenciler olabilir.
Bu belge shred ve aşağıdaki gösterildiği gibi veritabanında kaydetmek istediğinizi varsayalım:
Kaydetme <Öğrenci> Students verilerde tablo.
Kaydetme <Sınıf> Courses verilerde tablo.
Kaydetme m:n ilişki veri Öğrenci sınıfı, the CourseAttendence arasındaki tablo.Ek çalışma değerlerini ayıklamak için gereklidir.Bu bilgileri almak ve bunları saklamak için tablo, bu saklı yordamları kullanın:
Insert_Idrefs_Values
Kuşkusuz, KIMLIK ve Öğrenci No CourseAttendence tablosuna ekler.
Extract_idrefs_values
Her öğrenci kimliklerinden her ayıklar. <Kurs> Öğe. Bir kenarı tablo, bu değerleri almak için kullanılır.
Adımlar şunlardır:
-- 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
j.XML verileri kodlanmış olan base64 ikili alınıyor
Ikili veri, sık base64 kodlaması kullanılarak XML biçiminde eklenir.Bu XML shred OPENXML kullanılarak base64 olarak kodlanmış veri alıyorsunuz.Bu örnek göster base64 olarak kodlanmış verileri geri ikiliye dönüştürme için CLR işlev yazın.
Örnek bir CLR işlev oluşturmak için şu adımları izleyin:
Aşağıdaki C# kodu base64 olarak kodlanmış verileri ikiliye dönüştürür.Kod base64.cs, c:\temp klasördeki bir dosyaya kaydedin.
using System; public class Sample { public static byte[] ConvertBase64ToBinary(string base64String) { if (base64String == null) { return null; } return Convert.FromBase64String(base64String); } }Kodu derlemek ve bir kitaplığı oluşturma base64.dll.
csc /target:library base64.csBu alternatif derleme (Transact-SQL) CREATE, yönetilen uygulamayı bir modül oluşturun."Your_computer" bilgisayar adınızı değiştirmek zorunda kalırsınız.
drop assembly asbly_base64 go create assembly asbly_base64 from '\\your_computer\c$\temp\base64.dll' goCLR desteği etkinleştirilmiş olmayan, CLR etkinleştirmek için aşağıdaki ifadeleri çalıştırabilirsiniz:
sp_configure 'clr enabled', 1 reconfigure with override goOluşturma bir CLR işlev giriş olarak base64 olarak kodlanmış verileri alır ve ikili veriyi geri döndürür.Arama yoluyla bunu işlev Yönetilen uygulamayı modülünde.
create function fn_getBinaryFromBase64String( @s nvarchar(max) ) returns varbinary(max) as external name asbly_base64.Sample.ConvertBase64ToBinary go
Aşağıdakileri yaparak, CLR işlev artık sınayabilirsiniz:
Bir tablo ile örnek ikili verileri oluşturun.
Base64 kodlanmış ikili verileri içeren XML oluşturmak IÇIN XML sorguda ve IKILI BASE64 seçeneğini kullanın.
OPENXML kullanarak XML shred.OPENXML tarafından döndürülen verilerin base64 olarak kodlanmış veri olacaktır.Ardından, yeniden ikiliye dönüştürme için CLR işlevini çağırın.
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 fn_ getBinaryFromBase64String 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,
dbo.fn_getBinaryFromBase64String(Col2) as BinaryCol
FROM openxml(@h, '/T')
with (Col1 integer, Col2 nvarchar(max))
exec sp_xml_removedocument @h
go
Bu kaynaklanır.Ikili veri döndürdü Tablo T. özgün ikili veride
Col1 BinaryCol
----------- ---------------------
1 0x1234567890
See Also