read xml file contents - 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 2005 Forums

Transact-SQL (2005)

read xml file contents

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-07-11 : 04:19:27

There is a folder which contains several different xml files.

Question

for each xml file, how can I get the contents of the xml file and then pass it to a Stored Proc?

Is this to do with a sql function that takes the file path of the xml file, i.e. openxml or something similar?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-11 : 10:24:14

Which content? Entities? Values?

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 10:28:39

http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-07-11 : 11:10:23

quote:
Originally posted by spirit1

http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Yes, done that. Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-11 : 13:26:13

If you want only the values from the XML file, not the complete file, try something like this!


        EXEC sp_xml_preparedocument @hRows OUTPUT, @XML 

        INSERT theTable WITH (ROWLOCK) 
        ( 
                <col-list>
        ) 
        SELECT  <col-list> 
        FROM    OPENXML (@hRows, '/root/Invoice') 
                WITH   ( 
                                OrderID         VARCHAR(20)  'OrderNumber', 
                                Billing         MONEY        'InvoiceTotal', 
                                InvoiceDate     DATETIME     'InvoiceDate'
                        ) 

        EXEC sp_xml_removedocument @hRows

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources