XML function

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
tcflam
Posts: 58
Joined: Tue 01 Jan 2013 10:48

XML function

Post by tcflam » Tue 13 Aug 2013 13:40

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!

AndreyZ

Re: XML function

Post by AndreyZ » Wed 14 Aug 2013 09:35

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.

tcflam
Posts: 58
Joined: Tue 01 Jan 2013 10:48

Re: XML function

Post by tcflam » Wed 14 Aug 2013 10:06

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!

AndreyZ

Re: XML function

Post by AndreyZ » Thu 15 Aug 2013 08:37

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\").

tcflam
Posts: 58
Joined: Tue 01 Jan 2013 10:48

Re: XML function

Post by tcflam » Fri 16 Aug 2013 09:41

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? :cry:

procedure TVirtualTableFrame.btLoadClick(Sender: TObject);
begin
OpenDialog.InitialDir := ExtractFilePath(Application.ExeName) + 'VirtualTable';
if OpenDialog.Execute then
VirtualTable.LoadFromFile(OpenDialog.FileName);
end;

AndreyZ

Re: XML function

Post by AndreyZ » Fri 16 Aug 2013 10:18

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.

tcflam
Posts: 58
Joined: Tue 01 Jan 2013 10:48

Re: XML function

Post by tcflam » Sat 17 Aug 2013 14:09

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.

AndreyZ

Re: XML function

Post by AndreyZ » Mon 19 Aug 2013 09:08

If you want to use XPath queries, you should use SQL Server for handling them.

Post Reply