openxml kullanma
Bu konudaki örneklerde openxml bir satır kümesi xml belgesinin görünümünü 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östermek ancak metaproperties openxml belirtin.Metaproperties openxml belirtme hakkında daha fazla bilgi için bkz: Metaproperties openxml belirtme.
Örnekler
Veri alma, 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 dili ifade edilir.Desen bir öðe ya da öznitelik sona ererse, örneğin, bir satır olarak seçili her öðe ya da öznitelik düğümü için oluşturulan rowpattern.
The flags value provides default mapping.Yok, ColPattern belirtilen SchemaDeclaration, belirtilen eşleme flags kabul edilir.The flags value is ignored if ColPattern is specified in SchemaDeclaration.Belirtilen ColPattern öğesi merkezli, ya da öznitelik merkezli eşleme ve ayrıca taşma ve unconsumed veri davranışını belirler.
A.openxml ile basit bir deyim yürütme
Bu örnek xml belgesinde oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri.openxml deyim iki - müşteri bilgilerini alırsütun satır kümesi, CustomerID ve KişiAdı, xml belgesindeki.
İlk olarak, sp_xml_preparedocument saklı yordam, bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.
openxml deyim aşağıda gösterilmiştir:
rowpattern tanımlar (/ root/müşteri) <Customer> düğümlerini işlemek için.
The flags parameter value is set to 1 and indicates attribute-centric mapping.Sonuç olarak, xml özniteliği tanımlanmış satır kümesi içindeki sütunları eşleştirmek SchemaDeclaration.
De SchemaDeclaration, WITH yan tümce tümce tümce, belirtilen ColName değerlerle aynı karşılık gelen xml öznitelik adları.Bu nedenle, ColPattern parametresi belirtilen değil SchemaDeclaration.
deyim içindeki tüm sütunları alır satır kümesi openxml tarafından sağlanan.
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 verir:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
Çünkü <Customer> öğeleri ile aynı deyim yürütüldüğünde, tüm alt öğeleri yoktur bayrakları küme için 2 öğesi merkezli eşleme değerleri belirtmek için CustomerID ve KişiAdı için müşterileri boş olarak döndürülür.
@ XmlDocument olarak da olabilir xml türü veya (n)varchar(max) türü.
If <CustomerID> and <ContactName> in the XML document are subelements, the element-centric mapping retrieves the values.
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 verir:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
Not belge tanıtıcı tarafından döndürülen sp_xml_preparedocument toplu iş iş ve oturum süresince geçerli olur.
B.Satır kümesi sütunları ve xml öznitelikleri ve öğeleri arasında eşleştirme ColPattern belirtme
Nasıl XPath desen isteğe bağlı belirtilen bu örnek gösterir ColPattern satır kümesi sütunları ve xml öznitelikleri ve öğeleri arasındaki eşleşme sağlamak için parametre.
Bu örnek xml belgesinde oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri.openxml deyim bir satır kümesi olarak müşteri ve sipariş bilgileri alır (CustomerID, SiparişTarihi, ProdID, ve Miktar) xml belgesindeki.
İlk olarak, sp_xml_preparedocument saklı yordam, bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.
openxml deyim aşağıda gösterilmiştir:
- rowpattern (/ kök/Müşteri/sipariş/OrderDetail) tanımlayan <OrderDetail> düğümlerini işlemek için.
Resimde için flags parametre değeri küme 2 ve öğe merkezli eşleme gösterir.Eşleme ancak belirtilen ColPattern bu eşleştirme üzerine yazar.Diğer bir deyişle, belirtilen XPath desen ColPattern satır kümesi sütunlar öznitelikleri için eşleştirir.Bu sonuçlar , öznitelik merkezli eşleme.
De SchemaDeclaration, WITH yan tümce tümce tümcesinde ColPattern ile de belirtilen ColName ve ColType parametreleri.İsteğe bağlı ColPattern olduğu belirtilen XPath desen ve aşağıdakileri gösterir:
The OrderID, CustomerID, and OrderDate columns in the rowset map to the attributes of the parent of the nodes identified by rowpattern, and rowpattern identifies the <OrderDetail> nodes.Bu nedenle, CustomerID ve SiparişTarihi sütunları eşleştirmek için CustomerID ve SiparişTarihi özniteliklerini <Order> öğesi.
The ProdID and Qty columns in the rowset map to the ProductID and Quantity attributes of the nodes identified in rowpattern.
deyim içindeki tüm sütunları alır satır kümesi openxml tarafından sağlanan.
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 verir:
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
Olarak belirtilen XPath desen ColPattern satır kümesi sütun xml öğeleri eşlemek için de belirtilebilir.Bu sonuçlar , öğe merkezli eşleme.Aşağıdaki örnekte, xml belgesini <CustomerID> ve <OrderDate> , alt öğeleri olan <Orders> öğesi.Çünkü ColPattern belirtilen eşleme üzerine yazar flags parametresi, flags parametresi openxml belirtilmemiş.
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.öznitelik merkezli ve öğe merkezli eşleme birleştirme
Bu örnekte, flags parametresi küme 3 ve öznitelik merkezli ve öğe merkezli eşleme uygulanacağını gösterir.Bu durum, öznitelik merkezli eşleme ilk olarak uygulanır ve öğe merkezli eşleme için tüm sütunları henüz çözüldüğünü sonra 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 uygulanan CustomerID.Yok yok KişiAdı içindeki öznitelik <Customer> öğesi.Bu nedenle, öğe merkezli eşleme uygulanması gerekir.
D.Text() işlev XPath ColPattern belirtme
Bu örnek xml belgesinde oluşur <Customer> ve <Order> öğeleri.openxml deyim oluşan bir satır kümesi alır oid gelen öznitelik <Order> öğe tarafından tanımlanan düğümün üst Kimliğini rowpatternve yaprak değerini dize öğesinin içeriği.
İlk olarak, sp_xml_preparedocument saklı yordam, bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.
openxml deyim aşağıda gösterilmiştir:
rowpattern (/ kök/Müşteri/sipariş) tanımlayan <Order> düğümlerini işlemek için.
The flags parameter value is set to 1 and indicates attribute-centric mapping.Sonuç olarak, xml özniteliği tanımlanmış satır kümesi sütunları eşleştirmek SchemaDeclaration.
De SchemaDeclaration WITH yan tümce tümce tümcesinde oid ve Tutar satır kümesi sütun adlarını karşılık gelen xml öznitelik adlarının eşleşmesi.Bu nedenle, ColPattern parametresi belirtilmemiş.İçin Yorum XPath işlev satır kümesi sütun text(), belirtilen ColPattern.Bu belirtilen öznitelik merkezli eşleme üzerine yazar flags, ve yaprak değer sütunu içeren dize öğe içeriği.
deyim içindeki tüm sütunları alır satır kümesi openxml tarafından sağlanan.
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 verir:
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 yan tümce tümce tümcesinde belirtme
Bu örnek belirtir TableName WITH yan tümce tümce tümcesinde yerine SchemaDeclaration.Hiçbir sütun desenleri ve istediğiniz yapıyı 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 sipariş bilgileri alır (oid, Tarih, ve Tutar) xml belgesindeki.
İlk olarak, sp_xml_preparedocument saklı yordam, bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.
openxml deyim aşağıda gösterilmiştir:
rowpattern (/ kök/Müşteri/sipariş) tanımlayan <Order> düğümlerini işlemek için.
Yok yok SchemaDeclaration WITH yan tümce tümce tümcesinde.Bunun yerine, bir tablo adı belirtilir.Bu nedenle, tablo şema satır kümesi şeması olarak kullanılır.
The flags parameter value is set to 1 and indicates attribute-centric mapping.Bu nedenle, öðelerin özniteliklerine de tarafından tanımlanan, rowpattern, harita için satır kümesi sütun aynı ada sahip.
deyim içindeki tüm sütunları alır satır kümesi openxml tarafından sağlanan.
-- 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 verir:
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 tümce tümce openxml deyim içinde belirtilir.Sonuç olarak, bir kenar tablo biçiminde openxml tarafından üretilen satır kümesi vardır.deyim içinde kenar tüm sütunları döndürür tablo.
Örnek xml belgesini örnekte oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri.
İlk olarak, sp_xml_preparedocument saklı yordam, bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.
openxml deyim aşağıda gösterilmiştir:
rowpattern tanımlar (/ root/müşteri) <Customer> düğümlerini işlemek için.
WITH yan tümce tümce tümce sağlanır.Bu nedenle, openxml satır kümesi bir kenar tablo biçiminde döndürür.
deyim yer alan tüm sütun kenarını 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ç bir kenar tablo olarak döndürülür.Kenar tablo bilgilerini elde etmek için sorgular yazabilirsiniz.Örneğin:
Aşağıdaki sorgu sayısını verir Müşteri belgesindeki düğümlere.WITH yan tümce tümce tümce belirtildiğinden, openxml kenar tablo döndürür.Kenar tablo deyim sorgular.
SELECT count(*) FROM OPENXML(@docHandle, '/') WHERE localname = 'Customer'
Aşağıdaki sorgu xml öğesi türü düğümlerinin yerel adları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 sipariş ayrıntıları hakkında bilgi alır (ProductID, Miktar, ve SiparişNo) xml belgesindeki.
İlk olarak, sp_xml_preparedocument bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.
openxml deyim aşağıda gösterilmiştir:
rowpattern (/ ROOT/Customer/Order/OrderDetail/@ProductID) bir xml öznitelik ile sona ProductID.Sonuç kümesi içinde seçili xml belgesindeki her öznitelik düğümü için satır kümesi kümesi oluşturulur.
Bu örnekte, flags parametresi belirtilmemiş.Bunun yerine, eşlemeleri tarafından belirtilen ColPattern parametresi.
De SchemaDeclaration WITH yan tümce tümce tümcesinde ColPattern ile de belirtilen ColName ve ColType parametreleri.İsteğe bağlı ColPattern olan aşağıdakileri belirtmek için belirtilen XPath deseni:
XPath deseni (.) olarak belirtilen ColPattern için ProdID satır kümesi sütun içerik düğümü, geçerli düğümü tanımlar.Göre rowpattern olduğu belirtilen ProductID öznitelik <OrderDetail> öğesi.
The ColPattern, ../@Quantity, specified for the Qty column in the rowset identifies the Quantity attribute of the parent, <OrderDetail>, node of the context node, <ProductID>.
Benzer şekilde, ColPattern, .../../@OrderID, belirtilen için OID sütununda satır kümesi tanımlayan SiparişNo üst öznitelik <Order>, içerik düðümünün üst düğümün.Üst düğüm <OrderDetail>, bağlam düğümdür ve <ProductID>.
deyim içindeki tüm sütunları alır satır kümesi openxml tarafından sağlanan.
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 verir:
ProdID Qty OID
----------- ----------- -------
11 12 10248
42 10 10248
72 3 10283
H.Birden çok metin düğümleri içeren bir xml belgesi belirtme
Birden çok metin düðümleri xml varsa, belge, bir deyim ile bir ColPattern, text(), bunların tümünün yerine yalnızca ilk metin düğümü döndürür.Ö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 sonuç olarak ve değil TaU.
Ö.WITH yan tümce tümce tümcesinde xml veri türü belirtme
WITH yan tümce tümce tümcesindeki için eşleşen bir sütun deseni xml veri türü sütununda, yazılı mı, türlenmemiş gerekir dönmek boş bir sıra ya da bir dizi öğelerini, iþleme yönergeleri, metin düğümleri ve yorumlar.Veri için artığını bir xml veri türü.
Aşağıdaki örnekte, WITH yan tümce tümce tümcesindeki tablo şema bildirim içeren xml sütunları yazın.
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, ilettiğiniz bir xml türü değişkeni (@ x) sp_xml_preparedocument() işlev.
Bu sonucu verir:
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:
İçin SAdı sütun , varchar(30) türü değeri karşılık gelen gelen alınır <lname> öğesi.
İçin xmlname sütun , xml türü, aynı adı öğe değeri döndürülür.
Bayrak, 10'a küme.10 Anlamına gelir 2 + 8, burada 2 öğe merkezli eşleme gösterir ve 8 unconsumed xml verilerini yalnızca WITH yan tümce tümce tümcesinde tanımlanan taşma sütun eklenmesi gerektiğini belirtir.Varsa, küme bayrak WITH yan tümce tümce tümcesinde belirtilen taşma sütun için tüm xml belge 2'ye kopyalanır.
De durum WITH yan tümce tümce tümcesinde sütunu yazılı xml sütun, xml örnek için şemayı onaylamazsa, bir hata döndürdü.
J.Tek tek değerleri çok değerli özniteliklerini alma
Bir xml belge birden çok değerli öznitelikler olabilir.Örneğin, IDREFS öznitelik çok değerli olabilir.Bir xml belgesinde, birden çok değerli öznitelik değerleri boşlukla ayrılmış değerler içeren bir dize olarak belirtilir.Aşağıdaki xml belgesinde, attends öznitelik <Öğrenci> öğesi ve attendedBy öznitelik <sınıfı> çok değerli olduğunu.Birden çok değerli bir xml öznitelik tek tek değerleri alma ve her değer ayrı bir satırda veritabanında depolamak ek çalışma gerektirir.Bu örnek işlemi gösterilmektedir.
Bu örnek xml belgesi aşağıdaki öğelerden oluşur:
<Öğrenci>
The id (student ID), name, and attends attributes.The attends attribute is a multivalued attribute.
<Sınıf>
The id (class ID), name, and attendedBy attributes.The attendedBy attribute is a multivalued attribute.
The attends attribute in <Student> and the attendedBy attribute in <Class> represent a m:n relationship between the Student and Class tables.Bir öğrenci çoğu sınıfları alabilir ve bir sınıf çok sayıda Öğrenci olabilir.
Bu belge shred ve aşağıdaki gösterildiği gibi veritabanına kaydetmek istediğinizi varsayalım:
Kaydet <Öğrenci> veri Öğrenciler tablo.
Kaydet <sınıfı> kursları verileri tablo.
Kaydet ortası Öğrenci sınıfta CourseAttendence arasındaki ilişki veri tablo.Ek çalışma değerleri ayıklamak için gereklidir.Bu bilgileri almak ve bunun içinde saklamak için tablo, bu saklı yordamları kullanın:
Insert_Idrefs_Values
Tabii kimliği ve Öğrenci kimliği değerleri ekler CourseAttendence tablo.
Extract_idrefs_values
Her birinden ayrı ayrı Öğrenci kimlikleri ayıklar <kursu> öğesi.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ış ikili base64 alınıyor
İkili veri base64 kodlaması kullanılarak xml içinde sık sık yer alır.openxml kullanarak bu xml shred, base64 olarak kodlanmış veri alıyorsunuz.Bu örnek göstermek nasıl başa ikili base64 olarak kodlanmış verileri dönüştürmek için bir CLR işlev yazabilirsiniz.
Örnek clr işlev oluşturmak için şu adımları izleyin:
Aşağıdaki C# kodu base64 olarak kodlanmış veri ikiliye dönüştürür.Kod, c:\temp klasöründeki base64.cs dosyasına kaydedin.
using System; public class Sample { public static byte[] ConvertBase64ToBinary(string base64String) { if (base64String == null) { return null; } return Convert.FromBase64String(base64String); } }
Kodu derleyip bir kitaplık oluşturma base64.dll.
csc /target:library base64.cs
Kullanarak (Transact-sql) birleştirme oluşturmak, bir yönetilen uygulamayı modül oluşturun."İnclude" değiştirmek gerekecektir. Bilgisayarınızın adı ile.
drop assembly asbly_base64 go create assembly asbly_base64 from '\\your_computer\c$\temp\base64.dll' go
clr desteği etkinleştirilmiş yoksa yapabilirsiniz yürütmek clr etkinleştirmek için aşağıdaki deyimleri:
sp_configure 'clr enabled', 1 reconfigure with override go
Base64 olarak kodlanmış veri giriş olarak alır ve ikili veri döndüren clr işlev oluşturun.Çağırarak bunu işlev yönetilen uygulama modülü.
create function fn_getBinaryFromBase64String( @s nvarchar(max) ) returns varbinary(max) as external name asbly_base64.Sample.ConvertBase64ToBinary go
Şimdi aşağıdakileri yaparak CLR işlev test edebilirsiniz:
Örnek ikili verilerle bir tablo oluşturun.
for xml sorgu ve ikili BASE64 seçeneği base64 kodlanmış ikili verilere sahip xml oluşturmak için kullanın.
openxml kullanarak xml shred.openxml tarafından döndürülen veri base64 olarak kodlanmış veri olacaktır.Daha sonra çağrı CLR işlev ikiliye geri dönüştürmek için.
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
Sonuç budur.Döndürülen ikili verileri özgün ikili veride tablo t.
Col1 BinaryCol
----------- ---------------------
1 0x1234567890