Import XML data - 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

General SQL Server Forums

New to SQL Server Programming

Import XML data

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2011-11-04 : 17:24:14

Hello Sir ,

I have XML file .XSD and .XML file . I need to store that xml file into sql server from remote location .

Please sir help me out Sir .

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-05 : 13:11:00

you want to store XML as it is? if yes use OPENROWSET BULK as in below

http://msdn.microsoft.com/en-us/library/ms191184.aspx

if you want to shred xml and store the constituent data in table fields use .query or .nodes method or SSIS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaman
Posting Yak Master

213 Posts

Posted - 2011-11-07 : 11:30:24

quote:
Originally posted by visakh16

you want to store XML as it is? if yes use OPENROWSET BULK as in below

http://msdn.microsoft.com/en-us/library/ms191184.aspx

if you want to shred xml and store the constituent data in table fields use .query or .nodes method or SSIS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks Sir ,

Sorry Sir , Actually I want to store XML data from remote location in sql server .

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 11:39:37

quote:
Originally posted by yaman

quote:
Originally posted by visakh16

you want to store XML as it is? if yes use OPENROWSET BULK as in below

http://msdn.microsoft.com/en-us/library/ms191184.aspx

if you want to shred xml and store the constituent data in table fields use .query or .nodes method or SSIS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks Sir ,

Sorry Sir , Actually I want to store XML data from remote location in sql server .

Yaman

Thats the same thing i gave
check the link

it has an example for OPENROWSET ..BULK which does the same thing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaman
Posting Yak Master

213 Posts

Posted - 2011-11-07 : 12:09:43

quote:
Originally posted by visakh16

quote:
Originally posted by yaman

quote:
Originally posted by visakh16

you want to store XML as it is? if yes use OPENROWSET BULK as in below

http://msdn.microsoft.com/en-us/library/ms191184.aspx

if you want to shred xml and store the constituent data in table fields use .query or .nodes method or SSIS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks Sir ,

Sorry Sir , Actually I want to store XML data from remote location in sql server .

Yaman

Thats the same thing i gave
check the link

it has an example for OPENROWSET ..BULK which does the same thing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks Sir , It is possible by SSIS packages . ( Fetch xml data from remote server ) .

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 12:12:26

its possible using data flow task
see

http://blog.hoegaerden.be/2011/04/07/loading-xml-using-ssis/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaman
Posting Yak Master

213 Posts

Posted - 2011-11-07 : 16:05:31

quote:
Originally posted by visakh16

its possible using data flow task
see

http://blog.hoegaerden.be/2011/04/07/loading-xml-using-ssis/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks alot Sir ,

Now I am using SSIS but I have one more problem XML files is reside on different remote srevers how can I fetch xml data from all servers . I want to fetch one at a time . Is there any way to change xml file path dynamically or run time .

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 23:52:35

quote:
Originally posted by yaman

quote:
Originally posted by visakh16

its possible using data flow task
see

http://blog.hoegaerden.be/2011/04/07/loading-xml-using-ssis/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks alot Sir ,

Now I am using SSIS but I have one more problem XML files is reside on different remote srevers how can I fetch xml data from all servers . I want to fetch one at a time . Is there any way to change xml file path dynamically or run time .

Yaman

yep. its possible. add a variable to hold xml path in ssis package and add it to package configuration. then you can change the path at runtime by changing the config value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources