SQLCMD FOR XML AUTO

Hi Experts!

When I execute, from the Query Analyzer, the command:

select * from Art for xml auto

I get something like this:

<Art Articulo="000002" Descripcion1="Prueba 002" NombreCor...
 TipoCosteo="Promedio" Tipo="Normal" TipoOpcion="No" Acces...
Alterno="1.000000000000000e+000" Estatus="ALTA" UltimoCamb...
Precio5="518.0000" Precio6="618.0000" Precio7="718.0000" P...
ecio="NUEVO" wMostrar="1" SeCompra="1" SeVende="1" EsFormu...
d="1.000000000000000e+000" ProdUsuario="(Mismo)" ProdPasoT...
io="0" ProdVerPorcentaje="0" RevisionUsuario="DEMO" ProdMo...
 SolicitarPrecios="0" Espacios="0" EspaciosEspecificos="0"...
res="1" EspaciosHoraD="09:00" EspaciosHoraA="19:00" SerieL...
go="Resurtible" AnexosAlFacturar="0" Actividades="0" Valid...
do="0" ValidarCodigo="0" CostoIdentificado="0" Impuesto1Ex...
...

This is an legible XML.


Executing, from Start/Run, the command:

sqlcmd -E -SSQLXs -dSucursal -Q"select * from art for xml auto" -oD:\Datos\01.txt

generates the following output (the content of the file D:\Datos\01.txt is):

XML_F52E2B61-18A1-11d1-B105-00805F49916B                
--------------------------------------------------------
0x44084100720074006900630075006C006F004404520061006D0...
0x65006300630069006F006E004E0075006D00650072006F00490...
0x0000000000F03F028E014600028F01460002900146000293011...
0x000000025C4600025D4600025E4600025F46000260460002614...
0x0A00023A450000000040600A00023D4600023E4600023F46000...
0xF8F2000002244500000000C09F0000022544000000000000F03...
0x0000070053656D616E6173024F10E40400000000025010E4040...
0x0610E40400000000020710E404000005004F74726F73020810E...
0xE40400000D0028706F72206F6D6973696F6E29026810E404000...
0x6F6D6973696F6E2902694600026E10E40400000B00706F72204...
0x21000270450000000040CA2200027110E4040000090053494E4...
0x090053494E43414D42494F02764600027D4600027E460002800...
0x43414D42494F0246460102494601024A4600024B4600024C420...
0x01460002C301460002C4014F080000000000000159F902C5014...
0x040000050031303A303002840110E4040000050031383A30300...
0xE40400000000025B10E40400000000025C4600025D4600025E4...
0x0002004E6F021B4600021C4600021D4600021E4600021F46000...
0x44000000000000F03F028E014600028F0146000290014600029...
0x090050414D4152494C4C4F020210E40400000000020310E4040...
0x4B4600025010E40400001B0043616E7469646164204D696E696...
0x6E6173025010E40400000D004C6F746520706F72204C6F74650...
0x0000025A10E40400000000025B10E40400000000025C4600025...

(25 rows affected)



I mean, the XML result is not legible. That works fine for me. I will send this output through a Web Service.


At another moment of the process (in the Client), I need to turn that not legible XML, into a legible XML,
like which it is obtained from the Query Analyzer.

How I can convert this output into a legible XML?


I'm workink with Delphi 7, MS SQL 2005, WXP.
Thanks in advance.

P.S.: If you need some complete example the output file, only let me know.
Desarrollo_IntelisisAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Desarrollo_IntelisisConnect With a Mentor Author Commented:
I add this comment to be able to close this question. I'm going to close it giving the original points to aikimark, the Expert who was most participant in this question.
0
 
brejkCommented:
Try this:

sqlcmd -E -SSQLXs -dSucursal -Q"select * from art for xml auto, type" -oD:\Datos\01.txt
0
 
Desarrollo_IntelisisAuthor Commented:
Thanks brejk, but...

I don't need to convert the output in a legible XML.

What I need, is to convert the not legible format into a legible XML, in another time, in another place of the process.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
brejkCommented:
Ok, I did not understand your problem. I have no idea how to convert it in Delphi into legible XML format. But I would try some stream reader class (don't know the name of such classes in Delphi).
0
 
Desarrollo_IntelisisAuthor Commented:
brejk:

Do you know how to convert it, in another thing that is not Delphi?
Do you have the code?
Perhaps, I can translate it to Delphi. Or this code could give me some ideas.
0
 
brejkCommented:
I will check it this evening and will let you know.
0
 
brejkCommented:
Hi again,

I've done a very simple test in .NET and it seems ADO.NET has no problem at all in getting the proper XML from SELECT .... FOR XML queries. I just used SqlDataReader and simple ToString() method to display legible XML.

Have you tried to query ... FOR XML straight from Delphi code? Or you have to get the output from SQLCMD and then play with it in Delphi?
0
 
Desarrollo_IntelisisAuthor Commented:
Hi brejk!

I need to generate the result of query by SQLCMD from my server. That I cannot change it. I am not looking for an alternative to do that.

The result of this query, I am going to send it by WebService to the client, and need to be able to turn that result into a XML with which it can work in the client.

If you want, forget all the process. The question is:
How I can convert this:
0x44084100720074006900630075006C006F004404520061006D0...
0x65006300630069006F006E004E0075006D00650072006F00490...
...
into a legible XML?

That is all I need to know.
0
 
aikimarkCommented:
Although this is only a start to solving your problem completely, your task requires converting a Unicode string back into the XML ASCII string.

* Look at the characters represeted by the hex bytes you see in the binary XML output.
* Notice the column names.
* In most cases, the 00 bytes are throw-aways during this conversion process.

Here's a link to an EE question that involves a Hex to ASCII conversion.
http://www.experts-exchange.com/Q_20367280.html

========================
I suspect you will need to attach snippets of both the ASCII XML output as well as the binary XML output.
0
 
Desarrollo_IntelisisAuthor Commented:
Hi aikimark!

What is a snippet?
0
 
Desarrollo_IntelisisAuthor Commented:
Hi aikimark!

I tried to use the code that you suggested, but I can't obtain the original XML.

Do you need some example files?
0
 
aikimarkCommented:
This is an example of a snippet.  Check the
"Attach Code Snippet" box.  Of course, you would post the entire record, dropping the ellipses.
0x44084100720074006900630075006C006F004404520061006D0...
0x65006300630069006F006E004E0075006D00650072006F00490...

Open in new window

0
 
Desarrollo_IntelisisAuthor Commented:
I made an example that anyone can reproduce.
The QueryAnalyzer command is:
(use master)
select * from sysobjects where xtype = 'V' for xml auto

The SQLCMD command is:
sqlcmd -E -S<YourServer> -dmaster -Q"select * from sysobjects where xtype = 'V' for xml auto" -oC:\OutputTest.txt

I attach two example files:
XMLAuto.xml
OutputTest.txt

I hope this could help you to help me.

XMLAuto.txt
OutputTest.txt
0
 
aikimarkCommented:
From the first line of the binary file, I get the following characters if I ignore the 00 characters:
DnameDidDxtypeDuidDinfoDstatusDbase_schema_verDreplinfoD
parent_objDcrdateDftcatidD
schema_verDstats_schema_verDtypeDuserstatDsyss

There is a x44 byte followed by a length value (byte) for the field name.  I'm not sure where the rest of the field names are.

In the rows that follow, I would expect to see the values for these columns.  However, I don't see anything like the name data.  Most of the rest of the columns are numeric in nature, so that should be easy enough to represent with a byte or two (four, eight).
0
 
aikimarkCommented:
correction.  I don't see ALL of the name column values.
I do see:

COLUMNS
dm_exec_cached_plans
dm_os_memory_cache_clock_hands
dm_os_worker_local_storage
dm_tran_locks
symmetric_key - missing "s"
conversation_groups
database_principal_aliases
check_constraints
http_endpoints
sysoledbusers
sysusers
0
 
aikimarkCommented:
Since your binary XML is a fixed width, I suspect that some of the actual content is being truncated by the output engine.

===================
This seems to be a typical data representation scheme.  Define the columns (metadata) at the beginning of the output and follow it with the actual data.  If you save a TClientDataSet in XML format, you will see something similar, except that it isn't a binary representation.  It is the MyBase format.
0
 
Desarrollo_IntelisisAuthor Commented:
aikimark:

All this:

DnameDidDxtypeDuidDinfoDstatusDbase_schema_verDreplinfoD
parent_objDcrdateDftcatidD
schema_verDstats_schema_verDtypeDuserstatDsyss

finally are Field Names: name, id, xtype, uid, info, replinfostatus, base_schema_ver, replinfo parent_obj,
crdate ftcatid schema_ver, stats_schema_ver, type, userstat, etc.

In the other hand:

dm_exec_cached_plans, dm_os_memory_cache_clock_hands, dm_os_worker_local_storage,
dm_tran_locks, symmetric_key - missing "s", conversation_groups, database_principal_aliases,
check_constraints, http_endpoints, sysoledbusers, sysusers, etc.

are Field Values.

So, you are getting Field Names and Field Values.

What algorithm you are using to obtain those values?

Which is the meaning of 0x44 or 0x00 or 0x02 or etc. in the beginning of each line of the output file?
Or is 0x4, 0x0, 0x0, etc.?

0
 
Desarrollo_IntelisisAuthor Commented:
I mean:
What algorithm you are using to obtain those Field Values and Field Names?
0
 
aikimarkCommented:
I'm just using a little VB code, since I'm currently working in that development environment.

Basically, I copy/paste the hex digit strings into a string variable, loop through the string, picking every two characters.  I convert two characters into a hex value and then display the character for that numeric value.

Since there were only 15 lines in your hex output, this was not too difficult.

I must assume that the 0x44 is some kind of value that is understood by SQL Server and ADO.
0
 
Desarrollo_IntelisisAuthor Commented:
Sorry aikimark.

I tried it, but it does not work for me. I can't get the original XML. So my problem is still without being solved.

Anyway, thank you very much for the effort.
0
 
aikimarkCommented:
I can only help you so far in the reverse engineering of the binary XML format.  Sorry.  I was trying to get you to a point where you understood the meaning of the bytes you were seeing.  Once you understood the binary, you would have to reconstruct the ASCII XML.

The only solutions I might offer are to render the output twice, once in ASCII and then in binary.

This is a long shot speculation solution...see if any of the Delphi database controls or XML controls or class libraries can understand the binary stream.  If you find something that understands the binary format, you might be able to use that control/library to render it in ASCII format.
0
 
Desarrollo_IntelisisAuthor Commented:
Well, I tried with several Delphi components and nothing.

Come on Experts!  Nobody else is interested in this question?

I am going to increase the points. Of 125 to 250, but read the following information:

1 - I don't need to change my output.

2 - If somebody offers me a code to turns output to original XML, the points go for her/him.

In the development of this question, I believe that it is the information necessary and sufficient to enterder it correctly. Even so, if you need more information, do not doubt in asking for it.
0
 
Desarrollo_IntelisisAuthor Commented:
So?  Nobody?

Do you need more points? Just let me know.

If I have no answer, tomorrow I will delete this question.
0
 
aikimarkCommented:
I'm not sure how to advise you on this.  You are still on the first expanded page of Delphi questions, so other Delphi experts should be seeing it.  I don't know if the points affect the other experts' responses.  You might try bumping up to 500, since you are asking for an expert to do significant coding for you instead of helping you (like I've been doing).

Barring that, you might try RentACoder.com

If you can't get an answer here at EE, then request a refund of your points with a close of the question.
0
 
Desarrollo_IntelisisAuthor Commented:
Is true, aikimark. You have tried to help to me, which I thank very much, but still with your help, I have not been able to solve my problem.

So, Experts, accepting the suggestion of aikimark, the points go to 500.
0
 
aikimarkCommented:
@Desarrollo_Intelisis

While you are waiting for some Delphi experts' responses, can you tell me WHY you can't render the XML output in both ASCII and binary formats?

I realize that this solution is not what you had in mind, but it would solve your problem with the least amount of programming.  I just don't understand your constraints.
0
 
Desarrollo_IntelisisAuthor Commented:
Of course, aikimark, less than I can do is to explain my reasons.

We (I mean, in my work), already obtain the XML in ASCII. This is as we are working right now.

This XML travels by a WS to a client and there it is used. Part of the problem is that some XML are very large (huge, some times) and the format that generates SQLCMD is of so large minor that the XML ASCII, besides which, we cannot send the XML like so, since, when traveling by the network, the information of the XML would be visible for anyone.

Any way, there are several reasons. We already thought several ways. It is not a whim.

If we found the way to translate the binary exit of the SQLCMD to a XML ASCII, that would facilitate the work that already we are doing.
0
 
aikimarkCommented:
1. Are you aware that the data streams can be encrypted?  This applies to both the ASCII and binary formats.  Don't think you are any safer from packet sniffing by the binary format.

2. Moreover, there are some adequate compression routines available to web service programs.
0
 
Desarrollo_IntelisisAuthor Commented:
Yes, we already are using all these. And the process is complex. If we can convert the SQLCMD output to the original XML, the process is simplified enormously.
0
 
Desarrollo_IntelisisAuthor Commented:
Well, again, if I have no answer, tomorrow I will delete this question.
0
 
aikimarkCommented:
Rather than delete this question, I recommend that it be closed with points refunded.
0
 
aikimarkCommented:
It would be nice if the moderators would draw the attention of the Delphi experts community to this question prior to final disposition.
0
 
nmcdermaidCommented:
Thinking laterally, and referring to a remark 20864487:

>>
I've done a very simple test in .NET and it seems ADO.NET has no problem at all in getting the proper XML from SELECT .... FOR XML queries. I just used SqlDataReader and simple ToString() method to display legible XML.
>>

If its possible, why don't you compile a console .Net application which will do the conversion for you (as per that comment), and just do use that? What platorm do you need to convert the binary XML > Ascii XML on? If it will run a .Net console app then you can do that.

Its possible that that output is just unicode. Have you tried opening the output file up in a text editor that can view unicode? You might see your XML right before your eyes.
0
 
2266180Commented:
"I made an example that anyone can reproduce.
The QueryAnalyzer command is:
(use master)
select * from sysobjects where xtype = 'V' for xml auto

The SQLCMD command is:
sqlcmd -E -S<YourServer> -dmaster -Q"select * from sysobjects where xtype = 'V' for xml auto" -oC:\OutputTest.txt

I attach two example files:
XMLAuto.xml
OutputTest.txt"

I looke dat the files and I can say from the start that the output is not the same.
in xmlauto.xml there are over 40 rows (I got bored counting them when I reached 40). In OutputTest.txt there are only 15. AND, as aikimark noted, it's truncated. each row is 514 bytes in length.

I don't have sql server, so I cannot test, but as always, MSDN is a good place to look. so have you tried other options beside auto? http://msdn2.microsoft.com/en-us/library/aa226523(SQL.80).aspx

some other idea. I've worked with a company that developed applicaitons fro mobile stuff and they had to sycnhronize data between the device and server. now, obviously, the internet connection for devices is not a big bandwidth something, so they found a way of cutting down xml size by using a special xml format whose name is slipping my mind right now, but I will tell you what it is basically about: all nodes and all attribute names are numbered.
after doing this translation, the size of your xml will drop dramatically with about 30% and upto 50% or even more. it all depends on the names, obviously :)
and then you can apply a compression algorithm and reduce the size even more.
0
 
aikimarkCommented:
to see more, use one of these switches:
  [-y variable length type display width]
  [-Y fixed length type display width]

http://www.yukonxml.com/Reference/default.aspx?t=SQLCMD

=================
@nmcdermaid

Part of the XML binary data IS in unicode, especially the field names and text values.
0
 
Desarrollo_IntelisisAuthor Commented:
Hi All!

My intention, is not to find an alternative to the problem posted in the question, because already we are using an alternative, the one of the complete XML in ASCII.

I wanted to see if some Expert could give us the solution to turn the Output.txt into the original XML. That's all.

Perhaps, it is not possible.

Any way, thanks all of you for your help.
0
 
2266180Commented:
as long as the output is incomplete, it's impossible. try aikimarks sugggestion with -y, maybe that outputs everything
0
 
Desarrollo_IntelisisAuthor Commented:
Thanks, ciuly, but still supposing that the Output.txt was generated complete (I tried with -y4096 and it seems complete), still we do not have a way to turn that to the original XML.
0
 
2266180Commented:
well, I for example dont' have a goot input to work on and nor have sql server to generate one. so I cannot make you anything that will do that. why not post a complete xml?
0
 
Desarrollo_IntelisisAuthor Commented:
ciuly:

The original XML is the same that the one I already attach before (XMLAuto.txt).

I say that this new one is complete, because the data of each line finish before the length of the line.

OutputTestComplete.txt
0
 
Desarrollo_IntelisisAuthor Commented:
ciuly:

The XMLAuto.txt it is just a reference.
0
 
2266180Commented:
so in OutputTestComplete.txt is not the same data as in XMLAuto.txt?
it would be better if you could post a text file with clear xml and one with binary having the same data. that way we can see the corelation correctly.
0
 
Desarrollo_IntelisisAuthor Commented:
ciuly: Yes, it is the same data.
0
 
nmcdermaidCommented:
Base roughly on this thread here:

http://www.mydatabasesupport.com/forums/sqlserver-programming/362214-exec-linkedserver-dbo-forxml_proc-why-do-i-receive-binary-data.html

You might be able to:

1. Import your binary XML file back into a SQL Server field of type VARBINARY(MAX)
2. Convert it to Varchar

and if you're lucky this will convert your nasty binary XML steram into a nice varchar and you can see what it was originally.


Just an idea, I don't have time to try it now unfortuntately, but if you post back maybe I will find time.
0
 
nmcdermaidCommented:
Also this SQL 2005 help reference

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm

has some info on conversion from string input to XML data, which may also help.
0
 
Desarrollo_IntelisisAuthor Commented:
Sorry, nmcdermaid.

With "for xml auto, type", it does not work for me.

And I don't know how to "Import your binary XML file back into a SQL Server field of type VARBINARY(MAX)" and "Convert it to Varchar"

In the address "ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/..." something is bad. I can't load it.
0
 
aikimarkCommented:
@nmcdermaid

What do you think of this article?
http://www.informit.com/articles/article.aspx?p=102307&seqNum=16

This might be an applicable utility to explore:
http://software.techrepublic.com.com/download.aspx?docid=268478

I've seen some references to ADO handling of XML data.  However, I haven't found any good examples of using ADO routines to render the binary XML back into ASCII XML.
0
 
Desarrollo_IntelisisAuthor Commented:
aikimark:

I don't know how this article could help me.

And the FI.exe? Well, I registered In TechRepublic, download the FI.exe and try. Nothing. It does not work for me.
0
 
aikimarkCommented:
@Desarrollo_Intelisis

The linked document,
a87d0850-c670-4720-9ad5-6f5a22343ea8.htm,
is a description of the CAST() and CONVERT()
T-SQL functions.

=================
It looks like the SQL Server binary format might be ASN.1 instead of Fast Infoset.  I hope it is close to the TDS (Tabular Data Stream, wire transmission) format used by the MDAC and SQL Server.

This might be a useful ASN.1 utility:
http://www.shareup.com/ASN.1_Decoder-download-22922.html

I'm not sure what TDS format SQL Server 2005 uses.  There is a library that supports several TDS versions.
http://www.freetds.org/

Be aware that Microsoft is now licensing the TDS format.
0
 
aikimarkCommented:
Look at DataDirect's StylusStudio.com
0
 
Desarrollo_IntelisisAuthor Commented:
All this, are things that I do not understand very well. That is why I asked for help of the Experts.

Again, my intention, is not to find an alternative to the problem posted in the question, because already we are using an alternative, the one of the complete XML in ASCII.

I wanted to see if some Expert could give us the solution to turn the Output.txt into the original XML. That's all. I need the prescription (la receta de cocina), step by step. If not, it does not serve to us.

You have two files to test, the original XML and output of the SQLCMD.

I have seen in many other questions, that Experts generate code to solve the problem.
This is a question of 500 points. I need some code in Delphi (of preference) that turns the output into the XML. It could be in VB and I'll try to translate it.

Again, perhaps, it is not possible.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
If you are going to use SQLCMD to get the output for the XML data, you need to use :XML ON as part of your query.

The only way right off that I have been able to get it to work is by using an INPUT file -ic:\dir\input.sql and the contents are in the code snippet.

Also to note, that the command follows the snippet.  
I am trying to find the way to do this with just a query in the command, and I will post it when I get that syntax.


:XML ON
SELECT * FROM sysobjects FOR XML AUTO
 
sqlcmd -E -S servername -ic:\dir\input.sql -oc:\temp\output.txt

Open in new window

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Follow-up.  You will have to use the input file, because :XML ON must be on it's own line and cannot be followed by a TSQL Query, so it must be in a file because it would all be one line in the -Q statement.
HTH.
0
 
Desarrollo_IntelisisAuthor Commented:
Thanks, dbaduck, but my only problem is to turn the output into the original XML, at another moment of the process, another machine different from where it is generated.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
I am not sure I understand what you mean.  This is a very long thread and it goes in many different directions.  sqlcmd.exe can give you XML.

The above command I gave  you turns the XML into the same output as in Query Analyzer.  Help me know what is missing now.
0
 
aikimarkCommented:
@dbaduck

Will your steps convert a binary XML (TDS format) into ASCII XML?  If so, then all that is needed is a SQL Server Express installation, or equivalent) on the remote PC.  

@Desarrollo_Intelisis

The dbaduck approach would be to fool a remote (stand-alone) instance of SQL Server into thinking that it was receiving a TDS and use T-SQL to reverse it.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
The data that comes back is a stream, and if you are using sqlcmd you need to use the :XML ON so that you get actual XML instead of a stream.  If you are trying to use the stream that comes back then I would have to do some research to find out how to turn the stream into XML.

I just need to know what you actually want to use, the actual XML output, or the TDS stream and convert it to XML.
0
 
Desarrollo_IntelisisAuthor Commented:
dbaduck:

I need to convert this (Is this the TDS stream?):
0x44084100720074006900630075006C006F004404520061006D0...
0x65006300630069006F006E004E0075006D00650072006F00490...
0x0000000000F03F028E014600028F01460002900146000293011...
0x000000025C4600025D4600025E4600025F46000260460002614...

into an ASCII XML.

This convertion is in another time, another machine different from where it is generated, in the global proces.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
This data is the TDS stream and I will see if there is a way to get it back to actual ASCII, but not sure.
0
 
Desarrollo_IntelisisAuthor Commented:
dbaduck: Thanks.
0
 
aikimarkCommented:
@Desarrollo_Intelisis

Have you looked at the FreeTDS site?  If so, what do you think?  If not, why?
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark

Do you have the address (link) to the Free TDS Site? I google it, but I can't find it.
0
 
aikimarkCommented:
I posted it in my earlier comment (see above)
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark:

Well, it looks like for Unix only, isn't it?
0
 
aikimarkCommented:
Although the majority of installations are on some -ix operating system, the list of installations does include a few Windows clients.  There are no guarantees that it will work, but it is one avenue to explore when considering solution paths and their manpower requirements.

Your problem is one that has no immediate and cheap solution.  During my research, I don't limit my suggestions to those using Delphi/Pascal.

I did a little reverse engineering to help you understand the binary stream.  I don't have enough time and Delphi experience to write this code for you.
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark:

My intention never went to put to work too much to somebody. If some Expert had crossed this way, fine.

The idea, was to simplify the process that already we have at the moment.

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
The format of the output from SQLCMD is in a binary encoded format that is sent back for OLEDB clients and is not yet documented according to a source I have.  So good luck in breaking it out into legible XML.  I am not that good of a bit-head to figure it out.

In order to get it into legible XML you must use :XML ON as I illustrated above and then you can send that XML over the wire.

Ben.
0
 
Desarrollo_IntelisisAuthor Commented:
@dbaduck

We already generated the legible XML, compressed it and sent it by the network.
The idea here, was to simplify the process that we already have, but anyway, thanks for the suggestion.
0
 
aikimarkCommented:
@Desarrollo_Intelisis

What is the relative size difference between a compressed binary XML stream and a compressed ASCII XML stream, using your compression method?

What compression method are you using?

When you state that the SQLCMD output is "very large (huge, some times)", are you talking about a few tens of thousands of bytes?  millions?  billions?

If you've successfully received the binary XML stream, WHY do you need to render it in ASCII format?
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark

We don't receive the binary XML stream. We generate ASCII XML, compressed, send, decompressed and use as ASCII XML in the Client.

My idea was to simplify this proces if I could find a way to convert binary stream into ASCII XML.

0
 
2266180Commented:
it might be a silly question, but can't you just import the binary xml? I mean, since it can generate it, it should be able to import it too.
0
 
Desarrollo_IntelisisAuthor Commented:
@ciuly

Well, if that is possible, I don't know how to do it.
0
 
2266180Commented:
well, if it's a good option for you, then now everybody knows of it. I am guessing that all of us were pretty concentrated upon the conversion problem that we didin't even bother to question it's need :)

so I am thinking that maybe the otehr experts that have sql server can now test this aspect of the problem as well :)
0
 
aikimarkCommented:
@Desarrollo_Intelisis

Please answer ALL of my questions.

=================
There appears to be a disconnect in our communcations.  I will accept that it might be on my end.

1. The TDS format is used to efficiently transfer data between Microsoft database 'components'

2. Unless you reverse engineer the TDS, use something like FreeTDS, or use a database layer like that from DataDirect, you will need to license TDS from Microsoft.

3. Why would you use SQLCMD within a webservice?  A webservice should consist of compiled code only.

4. I've been laboring under the assumption that you are facing a problem that requires you to use SQLCMD output.  It now appears that you "have an idea" whose context we haven't fully grasped.  You are asking a HOW question without also supplying the context of WHAT and WHY.  This forces us to guess about what's missing.

5. Do you own both the webservice and client components?  What are these components (purpose, scope, etc.)?
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark

I speak Spanish, I don't speak English thah good, so I will try to answer everything I understand.

First of all, we do not have a problem to send certain information to the Client. That process already we do it without problems. We also made the WS and the interphase of the client who is going to receive this information. We developed it in Delphi.

Depending on the information that the Client solicits, the WS orders to execute the SQLCMD to obtain this data and return it to the Client. So, why SQLCMD? Because is faster than to establish an ADO connection from the WS. We made a lot of test about this.

Since the SQLCMD generates th stream of much less size than ASCII XML, I thought about finding an easy, simple and fast way of, using that stream, being able to turn it ASCII XML and thus to use it in the Client.

We do not want more complications in our process. That why I only asked HOW, without giving more information of WHAT and WHY.
0
 
aikimarkCommented:
Before I comment, take a look at this product:
http://www.agiledelta.com/product_efx.html

====================
You still haven't answered my question about compression.  How?  Comparison of two different compressed stream sizes?

If you want to consume the TDS directly, why not bypass the WS and implement a client/server configuration?

<We do not want more complications in our process>
If you successfully receive data at the client, then WHAT is the problem you are trying to solve with your less complicated successfully working process?  
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark

We did not use stream (TDS stream or as it is called). We use only ASCII XML.

The difference in sizes between a XML that contains thousand of registries and many column and the information result of the SQLCMD generating stream of that same table, is very great. And if you are going to send information on the network, the smaller the better, isn't it?

Why don't send directly stream and it use? That is the reason of having asked in the Experts, because I don't know how to do it.

In our case, perhaps the Client has not even installed MSSQL Server. All we are going it to handle with ClientDataSet's in the Client. Why this way? Well, we decided it this is the way we want to do it.

As I mentioned above, I think that if could send the stream and I can turn it to XML in the Client, that would simplify the process that we have at the moment.

My intention, from the beginning, was to know if some Expert already had done this and that he could say to me: Do this.

Perhaps, as also already I mentioned at some moment, this is not possible. Or nobody has done this before. There is no problem. I remain with the process as I have it at this moment.
0
 
aikimarkCommented:
Well it is certainly possible to compress an ASCII XML stream before you send it out of the WS.  Then you expand (decompress) it and use the XML.

A TClientDataset expects its XML to be in a particular format (MyBase).

"the smaller the better"
Of course it is.  I haven't seen you describe your (ASCII XML) compression method, although you already stated "We already generated the legible XML, compressed..."

There are many online articles that describe various compression methods.  I've also posted a link to a third party software vendor whose product compresses ASCII XML streams.  I've also posted a link to DataDirect, which may provide you with the interpretation layer you need.

As far as I can tell, you would need to pretend as though the TDS were being sent from a database, instead of a WS, when the data arrives at your client's database controls.

A good first step will be to create a skeleton client on your development server box.  The skeleton merely needs to make a call to the SQL Server and show that it correctly displays the data from the XML stream.  Next, verify that it is receiving the binary stream and not the ASCII stream.  Also, pay attention to any additional protocol data, used for hand-shaking, that may accompany the database data.  Once you know that, you might try sending a sample binary data stream from a non-database source.  If that works, then you will have the individual pieces of the solution puzzle.

======================
You have mentioned two reasons that you need to use SQLCMD.  I can't comment on the function of the WS, but your second reason (raw data transport over the network) will still be a concern sending binary (TDS) XML.  

You need to compress and encrypt the data at the WS before you send it to the client.  This WILL be necessary to meet this transport data security requirement.  You might be able to turn on some transport layer security HTTP 1.1/2.0.
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark

I don't know if my necessity is really clear, since I see, with my poor English, that you suggest compression methods, protocols of data and other things. We don't need that.

Today, we use, in the Client, a connection to the WS, calling one of its functions. Then, the WS connect to a DB in the main server and extract data. This data is compress (indeed, with a ZIP component in Delphi) and send it back to the Client. The Client, recives this data (in a file) and process it.

This works fine. No problem at all.

I thought that if I can find a way to convert the "0x44084100720074006900630075006C00..." data in a single, simple, fast step, I could omit the ZIP process. That's all.

Maybe it is not possible o maybe nobody knows how to do it in a single, simple, fast step.
But it's OK. We can continue doing it like we already do it.
0
 
aikimarkCommented:
I've certainly posted several possible components that might facilitate what you want.  Please check them out.

Warning: Your transmitted binary (TDS) data will not be obfuscated and can be seen by others.  Remember that I started to reverse engineer the data stream almost immediately.  If this WS is behind the corporate firewall, you shouldn't have to worry about hiding your data from your own network nodes.
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark

Ok, you are all right.

I am going to try to download the components that you mention, but if they are not VCL component for Delphi, I am not going to know how to using them. Not in a fast and simple way.

If the use of those components make more complex my process, I prefer to remain as I am.
0
 
aikimarkCommented:
<If the use of those components make more complex my process, I prefer to remain as I am.>

That is a reasonable decision process.  You will probably need to pose your need to DataDirect support forum.  Since they've licensed TDS from Microsoft, they should be able to give you what you want without any programming changes.
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark

Ok. I am going to consult it with my boss.

Meanwhile, I believe that already I must solicit that this question is closed, since is open from the 02/08/2008.

If in Direct Data (StylusStudio.com) they give me a solution, I specifically post a question to assign you the points of this one.
0
 
aikimarkCommented:
@Desarrollo_Intelisis

Please do not close this question until you have finished your research.  The length of time from the opening of a question isn't a criterion for its closure.  There will be at least a three week quiet period before you are reminded of this thread by one of the clean-up crew.

Current participants and future readers alike will be interested in your findings and final solution.
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark

Ok. I'll let you know what happend with Direct Data.
0
 
aikimarkCommented:
and don't forget to look at
http://www.agiledelta.com/product_efx.html

at least to be able to compare size and speed and network load and both client and server processing between binary and ASCII XML formats.
0
 
Desarrollo_IntelisisAuthor Commented:

After a long weekend, trying to convince my boss, we decided not to work with elemnts of third parts.

If the answer is not the kind of "procedure/function" in Delphi (or VB and I will translate it) or a VCL component for Delphi, we remain as we are now.

As I imagine that is not going to happen, in a couple of days I am going to delete this question again.
0
 
aikimarkCommented:
Please request that this question be closed with your points refunded.  Even though your boss has rejected some of the possible solutions, this discussion will benefit future readers.
0
 
2266180Commented:
for what it's worth, I almost agree with aikimark. I would award points though, for the hard work of the experts, but that is up to the asker.
0
 
aikimarkCommented:
@ciuly

<for what it's worth, I almost agree with aikimark>

* it's not worth all that much. trust me. :-)
* almost agreement is so great, I'm whelmed.
* almost agreement sure beats pseudo agreement
0
 
Desarrollo_IntelisisAuthor Commented:
@aikimark

Yes, I want to "this question be closed with your points refunded", but there is only a Delete button, not "Refunded" button.

The previous time, that I asked to close it, they (the Site Admin people) suggested to me deleted it with that button.

Is there another way to close a question? Maybe first the "Add to knowledgebase" button?
0
 
aikimarkCommented:
make your request at EE Community Support.
0
 
2266180Commented:
you don't have such a button :) as aikimark said, you should post in communicaty support, and post the link ot the question so the mdoerators will know which one you are talking about ;)
0
 
aikimarkConnect With a Mentor Commented:
for future readers, another ODBC driver that understands TDS:
http://uda.openlinksw.com/odbc/st/odbc-sqlserver-st/
0
All Courses

From novice to tech pro — start learning today.