XML ??? ???? ?????? ??? XML ??? ?? ??? SQL ????? ??? ???? ???? ?? ??? ???? ????

???? ?????? ???? ??????
???? ID: 316005 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

XML ??? expressed ???? Microsoft SQL Server 2000 ??????? ??? ??? ???? ?????? ??? XML ??? ?? ????? ?? ??? ???? ?? ???? ??? ?? ???? outlines ????? ?? ???? ???? ?? ???? ?? ????? ??????? ??? ???? ?????? ??? XML ???? ?? ??? ???? ?? ???????? ???

???:??? ?? Microsoft SQL Server 2005 ?? ????? ?? ??? ???, SQL Server 2005 ???????? ?????? ??? "XML ???? ?????? ??? ??????" ???? ?? ??????

??????????

???? ??? ?? ???? ??? ??? ????? ?? ????? ????:
  • ?????? 1 ?? XML ??? ?? ??? SQL Server 2000 (SQLXML), ?? ??? ?? ????????
????? ?????? ???????:
  • XML ?? ????? ???

Create table to receive the data

Use the following steps to create a table to receive the data that the XML Bulk Load component processes.
  1. Create a database namedMyDatabasein SQL Server.
  2. Open SQL Query Analyzer, and then change the database toMyDatabase.
  3. ????? ???Customertable inMyDatabaseby running the following SQL statement in Query Analyzer:
    USE MyDatabase
    CREATE TABLE Customer (
       CustomerId INT PRIMARY KEY,
       CompanyName NVARCHAR(20),
       City NVARCHAR(20))
    					

Create the XML data source file

This is the sample data source code. Paste this XML into Notepad, and then save the file as 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>
				

Create the mapping schema file

This next file is a file that you use to map the format of the data source XML to the format of theCustomertable in the database. Paste this XML into Notepad, and then save the file as 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>
				

Create a VBScript program to execute the XML Bulk Load component

This is the script that uses the XML Bulk Load component to insert the three records you created in the "Create the XML Data Source File" heading into the table you created in the "Create Table to Receive the Data" heading by using the mapping schema discussed in the "Create the Mapping Schema File" heading. Paste this VBScript code into Notepad, and then save the file as 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
				
Correct theConnectionStringcredentials on the second line of the code so that the script can work with your SQL Server installation. If you do not correct line 2, the following error message occurs after you execute the script:
Error connecting to the data source

Run the VBScript program

Run the VBScript program C:\Insertcustomers.vbs to insert the three customer records into theCustomer?????? ???

Verify it works

In SQL Query Analyzer, switch to theMyDatabasedatabase, and then run this query:
SELECT * FROM Customer
				
Note that the three records created in the "Create the XML data source file" heading are now in theCustomer?????? ???

Alternate technique

The XML Bulk Load component is capable of:
  • Mapping an XML document to multiples tables by using a relationship specified in the XML schema file.
  • Generating table schemas before bulk loading.
  • Bulk loading from a stream.
  • Bulk loading in overflow columns.



??????

SQLXML Books Online; topic: "Performing Bulk Load of XML Data"

???

???? ID: 316005 - ????? ???????: 04 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • 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
??????: 
kbhowtomaster kbmt KB316005 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:316005

??????????? ???

 

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