I have a client application that generate a database into my SQL Server. The generated database contains a BLOB data (image data type) of an XML file. My question is how best would I be able to use and read the data inside the XML file (for generating a web report). Would it be possible to a good idea to export the BLOB data into an XML file on the disk and import the XML back into SQL as table database. If yes, how would I go about doing this? Would I be using BCP to export the BLOB out into XML file? Also is it possible to import the XML back without predetermining / pre-creating the columns in the table? Thanks!!
我有一个客户端应用程序,可以在我的SQL Server中生成数据库。生成的数据库包含XML文件的BLOB数据(图像数据类型)。我的问题是我如何才能最好地使用和读取XML文件中的数据(用于生成Web报告)。是否可以将BLOB数据导出到磁盘上的XML文件中,并将XML作为表数据库导回到SQL中。如果是的话,我该怎么做呢?我是否会使用BCP将BLOB导出到XML文件中?还可以在不预先确定/预先创建表中的列的情况下导回XML吗?谢谢!!
1 个解决方案
#1
0
Unless, there is a need to do indexing / filtering / other analysis on various columns, there is no need to split the xml into various columns.
除非需要对各列进行索引/过滤/其他分析,否则无需将xml拆分为不同的列。
SQL Server 2008 R2 has xml support and a single table with two column ID and XMLData should work for straight forward storage and retrieval, for example:
SQL Server 2008 R2具有xml支持,并且具有两个列ID和XMLData的单个表应该可用于直接存储和检索,例如:
CREATE TABLE T1(Col1 int primary key, Col2 xml)
INSERT INTO T values(1,'<ProductDescription ProductID="1">iPhone</ProductDescription>')
Refer to the msdn article "Implementing XML in SQL Server" for syntax and other help.
有关语法和其他帮助,请参阅msdn文章“在SQL Server中实现XML”。
#1
0
Unless, there is a need to do indexing / filtering / other analysis on various columns, there is no need to split the xml into various columns.
除非需要对各列进行索引/过滤/其他分析,否则无需将xml拆分为不同的列。
SQL Server 2008 R2 has xml support and a single table with two column ID and XMLData should work for straight forward storage and retrieval, for example:
SQL Server 2008 R2具有xml支持,并且具有两个列ID和XMLData的单个表应该可用于直接存储和检索,例如:
CREATE TABLE T1(Col1 int primary key, Col2 xml)
INSERT INTO T values(1,'<ProductDescription ProductID="1">iPhone</ProductDescription>')
Refer to the msdn article "Implementing XML in SQL Server" for syntax and other help.
有关语法和其他帮助,请参阅msdn文章“在SQL Server中实现XML”。