Reading an XML file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reading an XML file

Author

Topic

mibheenick
Starting Member

12 Posts

Posted - 2006-07-25 : 02:07:47

Hi everyone,

I have a XML file at a specific location say "C:\test\tempXML.xml". I only have the above as input.

The XML file looks approximately like this :
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table>
<CountId>1</CountId>
<IdPaiement>104004805</IdPaiement>
</Table>
<Table>
<CountId>2</CountId>
<IdPaiement>104004806</IdPaiement>
</Table>
</NewDataSet>

and i want to retrieve the info from the file as follows:
CountId IdPaiement
------- ----------
1 104004805
2 104004806

can anyone help me with this plz. Transact SQL codes will be most welcome.
Thx

Live together like brothers and do business like strangers.....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-25 : 02:36:24

see
http://www.nigelrivett.net/SQLTsql/ParseXML.html
http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

manohar
Starting Member

6 Posts

Posted - 2006-07-25 : 04:25:55

See the following T-SQL Code

declare @doci int
declare @doc4 varchar(1000)
set @doc4='<NewDataSet>
<Table>
<CountId>1</CountId>
<IdPaiement>104004805</IdPaiement>
</Table>
<Table>
<CountId>2</CountId>
<IdPaiement>104004806</IdPaiement>
</Table>
</NewDataSet>';
or

Set @doc4=(select * from openrowset(bulk
'C:\test\tempXML.xml',single_clob)
as XmlColumn)

exec sp_xml_preparedocument
@doci output, @doc4

SELECT * into test_xml
FROM OPENXML (@doci, '/NewDataSet/Table',1)
WITH (countID int 'CountId',idpaiement int 'IdPaiement')

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 04:45:03

If you XML file is of any size, or significant complexity, you will probably need to resort to a bulk import routine:

http://www.perfectxml.com/articles/xml/importxmlsql.asp#3

sp_xml_preparedocument runs out of steam once you get to a MB or two, IME.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources