如何在D7中解码XML Blob字段 [英] How to Decode XML Blob field in D7

问题描述

我在尝试将MS SQL Server 2014实例返回的XML数据解码到用D7编写的应用程序时遇到问题。（Indy的版本是它随附的版本，9.00.10） 。

I'm having a problem trying to decode XML data returned by an instance of MS SQL Server 2014 to an app written in D7. (the version of Indy is the one which came with it, 9.00.10).

更新最初写此q时，给我的印象是blob字段的内容必须是Base64解码，但这似乎是错误的。按照Remy Lebeau的建议，blob流在解码之前但之后没有在字段名称和字段值中包含可识别的文本。

Update When I originally wrote this q, I was under the impression that the contents of the blob field needed to be Base64-decoded, but it seems that that was wrong. Having followed Remy Lebeau's suggestion, the blob stream contains recognisable text in the field names and field values before decoding but not afterwards.

在下面的代码中，AdoQuery中的SQL只是

In the code below, the SQL in the AdoQuery is simply

从作者那里选择* au_lname ='White'对于XML Auto

Select * from Authors where au_lname = 'White' For XML Auto

Authors表是演示 pubs数据库中的那个表。我添加了 Where子句以限制结果集的大小，以便显示返回的Blob的十六进制转储。

the Authors table being the one in the demo 'pubs' database. I've added the "Where" clause to restrict the size of the result set so I can show a hex dump of the returned blob.

根据Sql Server OLH，当指定 For XML Auto时，返回数据的默认类型为二进制base64编码格式。如果我让IDE创建此字段，则AdoQuery的单个字段的数据类型为ftBlob。

According to the Sql Server OLH, the default type of the returned data when 'For XML Auto' is specified is 'binary base64-encoded format'. The data type of the single field of the AdoQuery is ftBlob, if I let the IDE create this field.

执行以下代码会生成异常 DecodeToStream中的大小不均。在调用 IdDecoderMIME.DecodeToString（S）时，字符串S的长度为3514，而3514 mod 4为2，而不是显然应该为0的0，因此例外。我已经确认该字段的值中的字节数为3514，因此变体的大小与字符串的长度之间没有区别，即两者之间没有任何变化。

Executing the code below generates an exception "Uneven size in DecodeToStream". At the call to IdDecoderMIME.DecodeToString(S), the length of the string S is 3514, and 3514 mod 4 is 2, not 0 as it apparently should be, hence the exception. I've confirmed that the number of bytes in the field's value is 3514, so there's no difference between the size of the variant and the length of the string, i.e. nothing has gone awol in between.

procedure TForm1.FormCreate(Sender: TObject);
var
  SS : TStringStream;
  Output : String;
  S : String;
  IdDecoderMIME : TIdDecoderMIME;
begin
  SS := TStringStream.Create('');
  IdDecoderMIME := TIdDecoderMIME.Create(Nil);
  try
    AdoQuery1.Open;
    TBlobField(AdoQuery1.Fields[0]).SaveToStream(SS);
    S := SS.DataString;
    IdDecoderMIME.FillChar := #0;
    Output := IdDecoderMIME.DecodeToString(S);
    Memo1.Lines.Text := S;
  finally
    SS.Free;
    IdDecoderMIME.Free;
  end;
end;

I' m使用以下代码：

I'm using this code:

procedure TForm1.FormCreate(Sender: TObject);
var
  SS : TStringStream;
  MS : TMemoryStream;
  Output : String;
begin
  SS := TStringStream.Create('');
  MS := TMemoryStream.Create;
  try
    AdoQuery1.Open;
    TBlobField(AdoQuery1.Fields[0]).SaveToStream(SS);
    SS.WriteString(#13#10);
    Output := SS.DataString;
    SS.Position := 0;
    MS.CopyFrom(SS, SS.Size);
    MS.SaveToFile(ExtractFilePath(Application.ExeName) + 'Blob.txt');
  finally
    SS.Free;
    MS.Free;
  end;
end;

Blob.Txt文件的十六进制转储看起来像这样

A hex dump of the Blob.Txt file looks like this

00000000  44 05 61 00 75 00 5F 00 69 00 64 00 44 08 61 00  D.a.u._.i.d.D.a.
00000010  75 00 5F 00 6C 00 6E 00 61 00 6D 00 65 00 44 08  u._.l.n.a.m.e.D.
00000020  61 00 75 00 5F 00 66 00 6E 00 61 00 6D 00 65 00  a.u._.f.n.a.m.e.
00000030  44 05 70 00 68 00 6F 00 6E 00 65 00 44 07 61 00  D.p.h.o.n.e.D.a.
00000040  64 00 64 00 72 00 65 00 73 00 73 00 44 04 63 00  d.d.r.e.s.s.D.c.
00000050  69 00 74 00 79 00 44 05 73 00 74 00 61 00 74 00  i.t.y.D.s.t.a.t.
00000060  65 00 44 03 7A 00 69 00 70 00 44 08 63 00 6F 00  e.D.z.i.p.D.c.o.
00000070  6E 00 74 00 72 00 61 00 63 00 74 00 44 07 61 00  n.t.r.a.c.t.D.a.
00000080  75 00 74 00 68 00 6F 00 72 00 73 00 01 0A 02 01  u.t.h.o.r.s.....
00000090  10 E4 04 00 00 0B 00 31 37 32 2D 33 32 2D 31 31  .......172-32-11
000000A0  37 36 02 02 10 E4 04 00 00 05 00 57 68 69 74 65  76.........White
000000B0  02 03 10 E4 04 00 00 07 00 4A 6F 68 6E 73 6F 6E  .........Johnson
000000C0  02 04 0D E4 04 00 00 0C 00 34 30 38 20 34 39 36  .........408 496
000000D0  2D 37 32 32 33 02 05 10 E4 04 00 00 0F 00 31 30  -7223.........10
000000E0  39 33 32 20 42 69 67 67 65 20 52 64 2E 02 06 10  932 Bigge Rd....
000000F0  E4 04 00 00 0A 00 4D 65 6E 6C 6F 20 50 61 72 6B  ......Menlo Park
00000100  02 07 0D E4 04 00 00 02 00 43 41 02 08 0D E4 04  .........CA.....

如您所见，其中一些是清晰可辨的（字段名称和内容），而某些则不是。是否有人能识别这种格式，并且知道如何将其清除为通过在SS Management Studio中执行相同的查询所得到的纯文本，即，如何从结果集中成功提取XML？

As you can see, some of it is legible (field names and contents), some of it not. Does anyone recognise this format and know how to clean it up into the plain text I get from executing the same query in SS Management Studio, i.e. how do I successfully extract the XML from the result set?

顺便说一句，我同时使用Sql Server的MS OLE DB提供程序和Sql Server Native Client 11提供程序，并使用Delphi Seattle来获得相同的结果（包括Blob.Txt文件的内容） D7。

Btw, I get the same result (including the contents of the Blob.Txt file) using both the MS OLE DB Provider for Sql Server and the Sql Server Native Client 11 provider, and using Delphi Seattle in place of D7.

鉴于该代码访问了外部数据库，因此该代码是我最接近MCVE的代码。

Given that the code accesses an external database, this code is the closest I can get to an MCVE.

更新＃2 如果我将Sql查询更改为

Update #2 The decoding problem vanishes if I change the Sql query to

select Convert(Text,
(select * from authors where au_lname = 'White' for xml AUTO
))

给出的结果（在 SS 中）

<authors au_id="172-32-1176" au_lname="White" au_fname="Johnson" phone="408 496-7223" address="10932 Bigge Rd." city="Menlo Park" state="CA" zip="94025" contract="1"/>

但是我仍然很想知道如何在不需要Convert（）的情况下使它工作。我注意到，如果我从Sql中删除Where子句，则返回的格式不正确的XML-它包含一系列节点，每个数据行一个，但是没有封闭的根节点。

but I'm still interested to know how to get this to work without needing the Convert(). I've noticed that if I remove the Where clause from the Sql, what is returned is not well-formed XML - it contains a series of nodes, one per data row, but there is no enclosing root node.

此外，我意识到我可以通过不使用 For XML Auto来避免此问题，我只是对如何正确执行此操作感兴趣。另外，一旦设法提取XML，就不需要任何解析XML的帮助。

Also btw, I realise that I can avoid this problem by not using "For XML Auto", I'm just interested in how to do it correctly. Also, I don't need any help parsing the XML once I've managed to extract it.

推荐答案

添加 TYPE指令，用于指定要返回的XML。

Add the TYPE Directive to specify that you want XML returned.

select * 
from Authors 
where au_lname = 'White' 
for xml auto, type

这篇关于如何在D7中解码XML Blob字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！