Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I read an XML file and store the data in XML to our table in SQL Server 2008?

share|improve this question
2  
If you give us some XML, we can show you how. –  gbn Feb 28 '11 at 7:03
    
please make it more specific. Update your question to show sample xml which you want to import into database. –  Coder Hawk Feb 28 '11 at 7:23
1  
you have 3 solutions here, pick one that suits you and mark as answer. That will help the answerer :-). –  Marian Mar 3 '11 at 12:42
    
Please mark helped answer. Now you have enough reputation to upvote. upvote = saying thank you :) –  Coder Hawk Mar 4 '11 at 12:18
    
Please accept and/or vote on helpful or final answers. meta.stackexchange.com/questions/5234/… and meta.stackexchange.com/questions/7237 –  gbn Aug 27 '11 at 12:41

3 Answers 3

This parse node based XML. It's different to read attributes but it isn't as common

I had this lying around as a demo with 3 slightly different XPath queries

DECLARE @foo XML

SELECT @foo = N'
<harrys>
    <harry>
        <fish>0.015000000000</fish>
        <bicycle>2008-10-31T00:00:00+01:00</bicycle>
        <foo>ü</foo>
    </harry>
    <harry>
        <fish>0.025000000000</fish>
        <bicycle>2008-08-31T00:00:00+01:00</bicycle>
        <foo>ä</foo>
    </harry>
</harrys>
'

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/*') x(item)
    CROSS APPLY
    x.item.nodes('./*') AS y(item)

SELECT
    CAST(CAST(x.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(x.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(x.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('harrys/harry') x(item)

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/harrys') x(item)
    CROSS APPLY
    x.item.nodes('./harry') AS y(item)
share|improve this answer
    
Thanks gbn. It is amazing –  pooja Mar 2 '11 at 4:15

I have tried it with above answer. Try it,

XML:

<?xml version="1.0" encoding="utf-8" ?> 
- <FundingSought xml:lang="en">
- <Fund>
  <FundName>sdfdsfd</FundName> 
  <FundValue>1</FundValue> 
  </Fund>
- <Fund>
  <FundName>dfdgfdg</FundName> 
  <FundValue>2</FundValue> 
  </Fund>
- <Fund>
  <FundName>fghghh</FundName> 
  <FundValue>3</FundValue> 
  </Fund>
- <Fund>
  <FundName>sdfdgg</FundName> 
  <FundValue>4</FundValue> 
  </Fund>
- <Fund>
  <FundName>hgfhh</FundName> 
  <FundValue>5</FundValue> 
  </Fund>
- <Fund>
  <FundName>fghgh</FundName> 
  <FundValue>6</FundValue> 
  </Fund>
- <Fund>
  <FundName>ghhhh</FundName> 
  <FundValue>7</FundValue> 
  </Fund>
- <Fund>
  <FundName>hfghh</FundName> 
  <FundValue>8</FundValue> 
  </Fund>
  </FundingSought>

SQL:

CREATE TABLE #XmlImportTest(
xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
)
GO

DECLARE @xmlFileName VARCHAR(300)

SELECT @xmlFileName = 'C:\FundingSought.xml'

--– dynamic sql is just so we can use @xmlFileName variable in OPENROWSET

EXEC('INSERT INTO #XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''', SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO


DECLARE @foo XML


SET @foo = (SELECT xml_data from #XmlImportTest)


SELECT
    CAST(y.item.query('data(FundName)') AS varchar(30)),
    CAST(y.item.query('data(FundValue)') AS char(25))

FROM
    @foo.nodes('/*') x(item)
    CROSS APPLY
    x.item.nodes('./*') AS y(item)
share|improve this answer
    
is this answer worked for you? I think you should update your question and add this xml in there! –  Coder Hawk Feb 28 '11 at 10:08
    
Yes Sandy. It's working fine. –  pooja Mar 2 '11 at 4:13

I'll just add an answer so that you know you've got another option. You can also use OPENXML to read xml data. This was the way to do it in older versions of SQL Server. It's not perfect, but it works. And it's easy to abuse :-). Just compare the plans of two identical xmls treated with XPATH queries (gbn's answer) compared to OPENXML or OPENROWSET. I will use an example from the MSDN article now, but you can get the full picture:

DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='

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

</ROOT>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/ROOT/Customer',1)

WITH (CustomerID varchar(10),

ContactName varchar(20))

share|improve this answer
    
Thanks. I will try it –  pooja Mar 3 '11 at 4:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.