convert xml file to sql server 2005 table?

how to convert a xml file开发者_开发技巧 in to sql server 2005 table?can any one help me?

do we have any query to convert xml file to sql server 2005 table

On SQL Server 2005 and up, you could also use this XQuery code instead of the bulky SQL XML Bulkload if you need to quickly do an ad-hoc import of a few rows. It's not quite as fast, but you won't have to create a schema etc. to use it:

DECLARE @Input XML
SET @Input = '<ROOT>
                 <Customers>
                     <CustomerId>1111</CustomerId><CompanyName>Sean Chai</CompanyName><City>NY</City>
                     ....
                 </Customers>
              </ROOT>'

INSERT INTO dbo.Customer(CustomerId, CompanyName, City)
  SELECT
      InputData.Customer.value('(CustomerId)[1]', 'int') 'Customer ID',
      InputData.Customer.value('(CompanyName)[1]', 'varchar(100)') 'Company Name',
      InputData.Customer.value('(City)[1]', 'varchar(50)') 'City'
  FROM
      @Input.nodes('/ROOT/Customers') AS InputData(Customer)

Works great for small chunks of XML, if you need to store those in SQL tables.

See the Books Online Introduction to XQuery in SQL Server 2005 for background info.

Assuming that you want to read the entities as a row and their attributes as the column data this Microsoft Support page describes the process.

Your table and XML data must match:

CREATE TABLE Customer (
   CustomerId INT PRIMARY KEY,
   CompanyName NVARCHAR(20),
   City NVARCHAR(20))

xml:

<ROOT>
  <Customers>
    <CustomerId>1111</CustomerId>
    <CompanyName>Sean Chai</CompanyName>
    <City>NY</City>
  </Customers>
  ...
</ROOT>

You then need to create a schema mapping file:

<?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>

and finally a script (in this case VBScript) to load the data:

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