convert xml file to sql server 2005 table?

Question

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

Answer 1

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

Answer 2

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.