Insert Rows on a table from xml file

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

Transact-SQL (2005)

Insert Rows on a table from xml file

Author

Topic

cmspot
Starting Member

44 Posts

Posted - 2008-02-09 : 08:17:14

Hello everybody ,
I am going to need Your valueable help again...

I would like to create a procedure that will accepts a path of an xml filename as parameter and then will load some rows in a table of my database . . . .

I really need an answer because i have no xml idea and i must complete my task very soon........

Thank you so much!!

I sell my mother in law.Is anybody interested?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-09 : 08:25:08

I hope this will help.

CREATE TABLE #temp (

dt DATETIME,

col1 XML

)

/*Use the OPENROWSET and BULK load to load an xml file into

the table. GETDATE() is used to populate the first column*/

<Root>

<Case>

<ID>73720</ID>

<Accepted>1</Accepted>

<AcceptedBy>'person' </AcceptedBy>

<AcceptedDate>2007-08-04T08:09:22.867</AcceptedDate>

<Returned>0</Returned>

<ReturnedBy />

<ReturnedReason />

<RelID>6976111</RelID>

</Case>

</Root>

INSERT #temp

SELECT GETDATE(), *

FROM OPENROWSET(BULK 'C:\sub.xml',

SINGLE_BLOB) AS x;

SELECT *

FROM #temp

SELECT col1.value('(/Root/Case/ID)[1]',

'nvarchar(1000)'),

col1.value('(/Root/Case/Accepted)[1]',

'nvarchar(1000)'),

col1.value('(/Root/Case/AcceptedBy)[1]',

'nvarchar(1000)'),

col1.value('(/Root/Case/AcceptedDate)[1]',

'nvarchar(1000)'),

col1.value('(/Root/Case/Returned)[1]',

'nvarchar(1000)'),

col1.value('(/Root/Case/ReturnedBy)[1]',

'nvarchar(1000)'),

col1.value('(/Root/Case/ReturnedReason)[1]',

'nvarchar(1000)'),

col1.value('(/Root/Case/ReturnedReason)[1]',

'nvarchar(1000)'),

col1.value('(/Root/Case/RelID)[1]',

'nvarchar(1000)')

FROM #temp

DROP TABLE #temp

cmspot
Starting Member

44 Posts

Posted - 2008-02-09 : 09:15:50

It is working But What if My xml File has 1000 rows?

How Can this be accomplished?

I sell my mother in law.Is anybody interested?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-09 : 09:33:07

might help you on this:-

http://technet.microsoft.com/en-us/library/ms345122.aspx

cmspot
Starting Member

44 Posts

Posted - 2008-02-09 : 10:00:01

thank you friend very much but
It is difficult for me to understand this because i have no idea with xml situation...

I sell my mother in law.Is anybody interested?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources