Page 1 of 1
XML function
Posted: Tue 13 Aug 2013 13:40
by tcflam
Hi,
Can SDAC using SQL XML function that handle Xpath? If yes, how about the performance? I found that the performance is not very well in SQL store procedure. Any method import the data from XML to virtual table?
Thanks!
Re: XML function
Posted: Wed 14 Aug 2013 09:35
by AndreyZ
You can use any XPath functions in queries which are supported by SQL Server. The performance of such queries greatly depends on your server capacity, but not on SDAC.
You can load a XML file or a stream containing XML data to the TVirtualTable component using the LoadFromFile or LoadFromStream method correspondingly. Please note that XML data must be in ADO-compatible format or in virtual table data format.
Re: XML function
Posted: Wed 14 Aug 2013 10:06
by tcflam
Hi AndreyZ,
Thanks for your information. Can you give me a simple sample how to load XML into virtual table? As I know, SQL XML function is poor performance because using DCOM methodologies. If using SAX methodologies, then it will faster than DCOM. If using Tclientdataset, then the performance should be the bestest. So I want to know about SDAC.
Thanks!
Re: XML function
Posted: Thu 15 Aug 2013 08:37
by AndreyZ
You can take a look at an example of loading of an XML file to TVirtualTable in the SDACDemo->VirtualTable demo. You can find SDACDemo projects in the SDAC_Install_Directory\Demos directory under Windows XP, and in the "My Documents\Devart\SDac for you IDE\Demos" directory under Windows Vista/7 (for example, "C:\Documents and Settings\All Users\Documents\Devart\SDac for your IDE\Demos\", or "C:\Users\Public\Documents\Devart\SDac for your IDE\Demos\").
Re: XML function
Posted: Fri 16 Aug 2013 09:41
by tcflam
I follow your information and check the code. But I can't see how to doing the field mapping in the sample code. The following code is the "Load" button in the demo program. I review all coding in VTable.pas that hasn't any field mapping between virtual table and XML Xpath. Am I any wrong or overlook something in the coding?
procedure TVirtualTableFrame.btLoadClick(Sender: TObject);
begin
OpenDialog.InitialDir := ExtractFilePath(Application.ExeName) + 'VirtualTable';
if OpenDialog.Execute then
VirtualTable.LoadFromFile(OpenDialog.FileName);
end;
Re: XML function
Posted: Fri 16 Aug 2013 10:18
by AndreyZ
If you need to obtain the result of a XPath query, you can use the TMSQuery component. Here is an example (query is taken from
http://msdn.microsoft.com/en-us/library ... .110).aspx ):
Code: Select all
begin
MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('SELECT CatalogDescription.query(''');
MSQuery1.SQL.Add('declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";');
MSQuery1.SQL.Add('<Product ProductModelID="{ (/p1:ProductDescription/@ProductModelID)[1] }">');
MSQuery1.SQL.Add('{');
MSQuery1.SQL.Add('/p1:ProductDescription/p1:Specifications/Weight');
MSQuery1.SQL.Add('}');
MSQuery1.SQL.Add('</Product>');
MSQuery1.SQL.Add(''') as Result');
MSQuery1.SQL.Add('FROM Production.ProductModel');
MSQuery1.SQL.Add('WHERE CatalogDescription is not null');
MSQuery1.Open;
ShowMessage(TMSXMLField(MSQuery1.Fields[0]).XML);
end;
TVirtualTable cannot load any XML data that can be obtained using XPath. As I wrote earlier, TVirtualTable can load XML data in ADO-compatible format or in virtual table data format. You should make XPath to produce XML data in one of these formats. After this, you will be able to load it to TVirtualTable.
Re: XML function
Posted: Sat 17 Aug 2013 14:09
by tcflam
Thanks for your information. I know how to use SQL. But I found it has performance problem. I want to fine tune that use the other method. So I want to use SDAC to fine tune it. Normally, I use the other method that can faster than SQL XPath at least 10 times. And need not to use any SQL DB.
Base on your reply, that's mean still need to use SQL Server to handle XML.
Re: XML function
Posted: Mon 19 Aug 2013 09:08
by AndreyZ
If you want to use XPath queries, you should use SQL Server for handling them.