Selection a Portion of database and export it to XML
-
Saturday, December 01, 2012 6:27 AM
I am using SQL Server 2005 as DB
Having databse with multiple table with their parent child relationship( One to many)
I want a query to the parent table and other child will also be filtered with the data related in the parent table and return multiple table parent and child with related data( No joining format) ( Actuatually a portion of the total database) in a dataset.
Then the dataset will be converted to XML file..
After that XMl data will be imported to a same schema database and will be modified respecct to there identity.
Can any one help me to get rid of this problem..
- Moved by Eileen ZhaoMicrosoft Contingent Staff Friday, December 07, 2012 9:10 AM (From:Data Mining)
All Replies
-
Friday, December 07, 2012 9:09 AM
Hi Amitava_Serampore,
You can use T-SQL to achieve you target, please see:
http://stackoverflow.com/questions/3809451/convert-tabular-data-to-xml-using-sql-server
http://stackoverflow.com/questions/61233/the-best-way-to-shred-xml-data-into-sql-server-database-columns
Thanks,
EileenEileen Zhao
TechNet Community Support
- Marked As Answer by Iric WenModerator Monday, December 10, 2012 9:44 AM
-
Friday, December 07, 2012 3:49 PM
If you are moving fiels between SQL Servers, then SSIS might be an easier way to do that. If the servers aren't connected then bcp with the native file format would also be good.
If you absolutely have to use XML, FOR XML AUTO might be the easiest way to do that. It reflects the relationships in joined tables, eg
SELECT SCHEMA_NAME(tables.schema_id) AS schemaName, tables.name AS tableName, columns.name AS columnName FROM sys.tables tables INNER JOIN sys.columns columns ON tables.object_id = columns.object_id FOR XML AUTO, ROOT('tables'), TYPE- Marked As Answer by Iric WenModerator Sunday, December 09, 2012 9:21 AM
- Unmarked As Answer by Iric WenModerator Sunday, December 09, 2012 9:21 AM
- Marked As Answer by Iric WenModerator Monday, December 10, 2012 9:44 AM
.png)
