I have inherited a database that has a table called "Resources" that contains a field called "FilePath". FilePath is a VarChar(100) and contains the server path and filename of an XML file on the server hard disk.
I need to join the data in the XML with rows in the "Resources" table (and other tables).
Is it possible to get a Stored Procedure to load the XML file into a temporary table?
Is loading it into a table the best way to do it - or can I join directly to the XML file somehow?
What is the best approach? (I know nothing about XML in SQL Server yet)
Thanks in advance,
Chiz.
Try the link below to use OPENXML in SQL Server, I am not sure about Temp tables I would try it to see if it is possible, just remember to drop the temp table explictly. Hope this helps.http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx|||Thanks Caddre,
At the moment the .NET code calls a Stored Procedure which JOINs the Resources table with some other tables and returns the result which goes into a DataSet.
In order to build a DataSet that can be displayed in a DataGrid the DataSet has to be looked at (one row at a time) and the FilePath 'field' used to load the XML file from disk into an XmlDocument. Then the right data in the right node has to be found and then finally when that is found it can be added to the DataSet.
Anyway, to cut a long story short, I reckon that if I can somehow get T-SQL to load an XML file from disk into a temporary table that it will be neater and more efficient.
Any other help appreciated,
Chiz.|||
The text below is from the BOL(books online) sp_xml_preparedocument System stored Procedure can be used, run a search for sp_xml_preparedocument in the BOL (books online). Hope this helps
(sp_xml_preparedocument
Reads the Extensible Markup Language (XML) text provided as input, then parses the text using the MSXML parser (Msxml2.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes (elements, attributes, text, comments, and so on) in the XML document.)
