Lm th? no ? nh?p kh?u XML vo SQL Server v?i cc thnh ph?n XML v?i s? l?ng l?n t?i

D?ch tiu ? D?ch tiu ?
ID c?a bi: 316005 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

TM T?T

D? li?u ?c th? hi?n trong XML c th? ?c n?p vo m?t Microsoft SQL Server 2000 c s? d? li?u b?ng cch s? d?ng cc thnh ph?n XML v?i s? l?ng l?n t?i. Bi vi?t ny v?ch ra cc b?c b?n c?n ph?i lm theo ? t?i d? li?u XML thnh m?t b?ng m ? t?n t?i trong c s? d? li?u.

Chu y N?u b?n ang s? d?ng Microsoft SQL Server 2005, xem cc ch? ? "XML v?i s? l?ng l?n t?i v d?" trong SQL Server 2005 sch tr?c tuy?n.

Yu c?u

S? d?ng cc b?c trong bi vi?t ny b?n c?n:
  • Web pht hnh 1 c?a XML cho SQL Server 2000 (SQLXML), ho?c sau ny.
Ki?n th?c c?n thi?t:
  • Ki?n th?c c?a XML.

T?o b?ng ? nh?n d? li?u

S? d?ng cc b?c sau ? t?o ra m?t b?ng ? nh?n d? li?u r?ng cc thnh ph?n XML v?i s? l?ng l?n t?i qu tr?nh.
  1. T?o m?t c s? d? li?u ?c ?t tn MyDatabase trong SQL Server.
  2. M? SQL Query Analyzer, v sau thay ?i c s? d? li?u ? MyDatabase.
  3. T?o ra m?t Khch hng trong b?ng MyDatabase khi ch?y cc l?nh SQL sau y trong truy v?n Analyzer:
    USE MyDatabase
    CREATE TABLE Customer (
       CustomerId INT PRIMARY KEY,
       CompanyName NVARCHAR(20),
       City NVARCHAR(20))
    					

T?o t?p tin ngu?n d? li?u XML

y l m? ngu?n d? li?u m?u. Dn XML ny vo Notepad, v sau lu t?p tin l C:/Customers.xml.
<ROOT>
  <Customers>
    <CustomerId>1111</CustomerId>
    <CompanyName>Sean Chai</CompanyName>
    <City>NY</City>
  </Customers>
  <Customers>
    <CustomerId>1112</CustomerId>
    <CompanyName>Tom Johnston</CompanyName>
    <City>LA</City>
  </Customers>
  <Customers>
    <CustomerId>1113</CustomerId>
    <CompanyName>Institute of Art</CompanyName>
  </Customers>
</ROOT>
				

T?o t?p tin l?c ? nh x?

Tranh ti?p theo l m?t t?p tin m b?n s? d?ng ? l?p b?n ? cc ?nh d?ng c?a cc d? li?u ngu?n XML ?nh d?ng c?a cc Khch hng b?ng trong c s? d? li?u. Dn XML ny vo Notepad, v sau lu cc t?p tin nh C:/Customermapping.xml.
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data" 
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" > 

   <ElementType name="CustomerId" dt:type="int" />
   <ElementType name="CompanyName" dt:type="string" />
   <ElementType name="City" dt:type="string" />

   <ElementType name="ROOT" sql:is-constant="1">
      <element type="Customers" />
   </ElementType>

   <ElementType name="Customers"  sql:relation="Customer">
      <element type="CustomerId"  sql:field="CustomerId" />
      <element type="CompanyName" sql:field="CompanyName" />
      <element type="City"        sql:field="City" />
   </ElementType>

</Schema>
				

T?o m?t VBScript chng tr?nh ? th?c thi cc thnh ph?n XML v?i s? l?ng l?n t?i

y l k?ch b?n m s? d?ng thnh ph?n XML v?i s? l?ng l?n t?i ? chn cc b?n ghi ba b?n t?o ra trong "Create the XML d? li?u t?p ngu?n" nhm vo b?ng b?n t?o ra trong nh?ng "t?o ra b?ng ? nh?n ?c the Data" tiu ? b?ng cch s? d?ng l?c ? nh x? th?o lu?n trong cc "t?o cc b?n ? Schema File"tiu ?. Dn m? ny VBScript vo Notepad, v sau lu cc t?p tin nh C:\Insertcustomers.VBS.
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=MySQLServer;database=MyDatabase;uid=MyAccount;pwd=MyPassword"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\customermapping.xml", "c:\customers.xml"
Set objBL = Nothing
				
Kh?c ph?c nh?ng ConnectionString credentials trn d?ng th? hai c?a m? ? cc k?ch b?n c th? lm vi?c v?i ci ?t SQL Server c?a b?n. N?u b?n s?a d?ng 2, cc sau thng bo l?i x?y ra sau khi b?n th?c hi?n cc t?p l?nh:
L?i k?t n?i v?i cc ngu?n d? li?u

Ch?y VBScript chng tr?nh

Ch?y chng tr?nh VBScript C:\Insertcustomers.vbs ? chn cc ba khch hng ghi vo cc Khch hng b?ng.

Ki?m tra xem n ho?t ?ng

Trong SQL Query Analyzer, chuy?n sang cc MyDatabase c s? d? li?u, v sau ch?y truy v?n ny:
SELECT * FROM Customer
				
Lu ? r?ng cc h? s ba t?o ra trong tiu ? "T?o t?p ngu?n d? li?u XML" v ang trong cc Khch hng b?ng.

Thay th? k? thu?t

Cc thnh ph?n XML v?i s? l?ng l?n t?i c kh? nng:
  • L?p b?n ? m?t ti li?u XML ? b?i b?ng b?ng cch s? d?ng m?t m?i quan h? ?c xc ?nh trong t?p tin l?c ? XML.
  • T?o ra cc b?ng l?c ? tr?c khi cc s? l?ng l?n t?i.
  • S? l?ng l?n t?i t? m?t d?ng su?i.
  • S? l?ng l?n t?i trong trn c?t.



TAI LIU THAM KHAO

SQLXML cu?n sch tr?c tuy?n; ch? ?: "th?c hi?n v?i s? l?ng l?n t?i c?a XML D? li?u"

Thu?c tnh

ID c?a bi: 316005 - L?n xem xt sau cng: 27 Thang Tam 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
T? kha:
kbsqlsetup kbhowtomaster kbmt KB316005 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:316005

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com