将XML中的Web数据传递到SQL服务器数据库的明智方法

After exploring several different ways to pass web data to a database for update purposes, I'm wondering if XML might be a good strategy. The database is currently SQL 2000. In a few months it will move to SQL 2005 and I will be able to change things if needed, but I need a SQL 2000 solution now.

在研究了几种将web数据传递到数据库以进行更新的方法之后，我想知道XML是否是个好策略。数据库当前是SQL 2000。在几个月后，它将转到SQL 2005，如果需要，我将能够改变一些东西，但是我现在需要一个SQL 2000解决方案。

First of all, the database in question uses the EAV model. I know that this kind of database is generally highly frowned on, so for the purposes of this question, please just accept that this is not going to change.

首先，该数据库使用EAV模型。我知道这类数据库通常是不受欢迎的，所以为了这个问题的目的，请接受这个不会改变的事实。

The current update method has the web server inserting values (that have all been converted first to their correct underlying types, then to sql_variant) to a temp table. A stored procedure is then run which expects the temp table to exist and it takes care of updating, inserting, or deleting things as needed.

当前的更新方法有web服务器插入值(所有这些值都先被转换为其正确的底层类型，然后是sql_variant)到临时表。然后运行一个存储过程，该过程期望临时表存在，并负责根据需要更新、插入或删除内容。

So far, only a single element has needed to be updated at a time. But now, there is a requirement to be able to edit multiple elements at once, and also to support hierarchical elements, each of which can have its own list of attributes. Here's some example XML I hand-typed to demonstrate what I'm thinking of.

到目前为止，一次只需要更新一个元素。但是现在，需要同时编辑多个元素，还要支持分层元素，每个元素都可以有自己的属性列表。下面是我手工输入的XML示例，演示了我的想法。

Note that in this database the Entity is Element and an ID of 0 signifies "create" aka an insert of a new item.

注意，在这个数据库中，实体是元素，ID为0表示“创建”，即插入新项。

<Elements>
  <Element ID="1234">
    <Attr ID="221">Value</Attr>
    <Attr ID="225">287</Attr>
    <Attr ID="234">
      <Element ID="99825">
        <Attr ID="7">Value1</Attr>
        <Attr ID="8">Value2</Attr>
        <Attr ID="9" Action="delete" />
      </Element>
      <Element ID="99826" Action="delete" />
      <Element ID="0" Type="24">
        <Attr ID="7">Value4</Attr>
        <Attr ID="8">Value5</Attr>
        <Attr ID="9">Value6</Attr>
      </Element>
      <Element ID="0" Type="24">
        <Attr ID="7">Value7</Attr>
        <Attr ID="8">Value8</Attr>
        <Attr ID="9">Value9</Attr>
      </Element>
    </Attr>
    <Rel ID="3827" Action="delete" />
    <Rel ID="2284" Role="parent">
      <Element ID="3827" />
      <Element ID="3829" />
      <Attr ID="665">1</Attr>
    </Rel>
    <Rel ID="0" Type="23" Role="child">
      <Element ID="3830" />
      <Attr ID="67"
    </Rel>
  </Element>
  <Element ID="0" Type="87">
    <Attr ID="221">Value</Attr>
    <Attr ID="225">569</Attr>
    <Attr ID="234">
      <Element ID="0" Type="24">
        <Attr ID="7">Value10</Attr>
        <Attr ID="8">Value11</Attr>
        <Attr ID="9">Value12</Attr>
      </Element>
    </Attr>
  </Element>
  <Element ID="1235" Action="delete" />
</Elements>

Some Attributes are straight value types, such as AttrID 221. But AttrID 234 is a special "multi-value" type that can have a list of elements underneath it, and each one can have one or more values. Types only need to be presented when a new item is created, since the ElementID fully implies the type if it already exists. I'll probably support only passing in changed items (as detected by javascript). And there may be an Action="Delete" on Attr elements as well, since NULLs are treated as "unselected"--sometimes it's very important to know if a Yes/No question has intentionally been answered No or if no one's bothered to say Yes yet.

有些属性是直接值类型，如AttrID 221。但是AttrID 234是一种特殊的“多值”类型，它可以在它下面有一个元素列表，每个元素都可以有一个或多个值。类型只需要在创建一个新项时显示，因为如果类型已经存在，那么逻辑符号就充分暗示了类型。我可能只支持传入已更改的项(如javascript所检测到的)。对于Attr元素也可能有一个动作=“Delete”，因为NULLs被视为“未被选中”——有时候，知道一个是/否的问题是否被故意回答为“否”或是否还没有人费心去回答“是”是非常重要的。

There is also a different kind of data, a Relationship. At this time, those are updated through individual AJAX calls as things are edited in the UI, but I'd like to include those so that changes to relationships can be canceled (right now, once you change it, it's done). So those are really elements, too, but they are called Rel instead of Element. Relationships are implemented as ElementID1 and ElementID2, so the RelID 2284 in the XML above is in the database as:

还有一种不同的数据，一种关系。此时，在UI中进行编辑时，它们通过单独的AJAX调用进行更新，但我希望包含这些调用，以便可以取消对关系的更改(现在，一旦更改，就完成了)。这些都是元素，但它们被叫做Rel而不是Element。关系的实现方式为eld1和eld2，因此上面XML中的RelID 2284在数据库中为:

ElementID 2284 ElementID1 1234 ElementID2 3827

电子分类号:2284电子分类号:1234电子分类号:3827

Having multiple children in one relationship isn't currently supported, but it would be nice later.

在一段关系中有多个孩子目前还不受支持，但以后会很好。

Does this strategy and the example XML make sense? Is there a more sensible way? I'm just looking for some broad critique to help save me from going down a bad path. Any aspect that you'd like to comment on would be helpful.

这个策略和示例XML有意义吗?有更明智的方法吗?我只是在寻找一些宽泛的批评，以帮助我避免走上一条糟糕的道路。你想评论的任何方面都是有用的。

The web language happens to be Classic ASP, but that could change to ASP.Net at some point. A persistence engine like Linq or nHibernate is probably not acceptable right now--I just want to get this already working application enhanced without a huge amount of development time.

web语言碰巧是经典的ASP，但是这可能会变成ASP。净。像Linq或nHibernate这样的持久性引擎现在可能是不可接受的——我只是想让这个已经工作的应用程序在没有大量开发时间的情况下得到增强。

I'll choose the answer that shows experience and has a balance of good warnings about what not to do, confirmations of what I'm planning to do, and recommendations about something else to do. I'll make it as objective as possible.

我将选择显示经验的答案，并对不应该做什么给出良好的警告，确认我计划做什么，以及对其他要做的事情提出建议。我会让它尽可能的客观。

P.S. I'd like to handle unicode characters as well as very long strings (10k +).

另外，我想处理unicode字符以及非常长的字符串(10k +)。

UPDATE

更新

I have had this working for some time and I used the ADO Recordset Save-To-Stream trick to make creating the XML really easy. The result seems to be fairly fast, though if speed ever becomes a problem I may revisit this.

我已经工作了一段时间，我使用ADO记录集Save-To-Stream技巧使创建XML变得非常简单。结果似乎相当快，但如果速度成为一个问题，我可能会重新考虑这个问题。

In the meantime, my code works to handle any number of elements and attributes on the page at once, including updating, deleting, and creating new items all in one go.

同时，我的代码可以同时处理页面上的任意数量的元素和属性，包括一次更新、删除和创建新项。

I settled on a scheme like so for all my elements:

我为我所有的要素决定了这样一个方案:

Existing data elements

现有的数据元素

Example: input name e12345_a678 (element 12345, attribute 678), the input value is the value of the attribute.

示例:输入名称e12345_a678(元素12345，属性678)，输入值是属性的值。
New elements

新元素

Javascript copies a hidden template of the set of HTML elements needed for the type into the correct location on the page, increments a counter to get a new ID for this item, and prepends the number to the names of the form items.

Javascript将类型所需的一组HTML元素的隐藏模板复制到页面上的正确位置，递增一个计数器以获取该项的新ID，并将该数字预写为表单项的名称。
```
var newid = 0;

function metadataAdd(reference, nameid, value) {
   var t = document.createElement('input');
   t.setAttribute('name', nameid);
   t.setAttribute('id', nameid);
   t.setAttribute('type', 'hidden');
   t.setAttribute('value', value);
   reference.appendChild(t);
}

function multiAdd(target, parentelementid, attrid, elementtypeid) {
   var proto = document.getElementById('a' + attrid + '_proto');
   var instance = document.createElement('p');
   target.parentNode.parentNode.insertBefore(instance, target.parentNode);
   var thisid = ++newid;
   instance.innerHTML = proto.innerHTML.replace(/{prefix}/g, 'n' + thisid + '_');
   instance.id = 'n' + thisid;  
   instance.className += ' new';
   metadataAdd(instance, 'n' + thisid + '_p', parentelementid);
   metadataAdd(instance, 'n' + thisid + '_c', attrid);
   metadataAdd(instance, 'n' + thisid + '_t', elementtypeid);
   return false;
}
```
Example: Template input name _a678 becomes n1_a678 (a new element, the first one on the page, attribute 678). all attributes of this new element are tagged with the same prefix of n1. The next new item will be n2, and so on. Some hidden form inputs are created:

示例:模板输入名称_a678变成n1_a678(一个新元素，第一个在页面上，属性678)。这个新元素的所有属性都带有相同的n1前缀。下一个新项目是n2，以此类推。一些隐藏的表单输入被创建:

n1_t, value is the elementtype of the element to be created n1_p, value is the parent id of the element (if it is a relationship) n1_c, value is the child id of the element (if it is a relationship)

n1_t，值是要创建的元素的元素类型n1_p，值是元素的父id(如果它是关系)n1_c，值是元素的子id(如果它是一段关系)
Deleting elements

删除元素

A hidden input is created in the form e12345_t with value set to 0. The existing controls displaying that attribute's values are disabled so they are not included in the form post. So "set type to 0" is treated as delete.

在格式e12345_t中创建一个隐藏的输入，值设置为0。显示该属性值的现有控件被禁用，因此它们不包含在表单post中。因此“将类型设置为0”被视为删除。

With this scheme, every item on the page has a unique name and can be distinguished properly, and every action can be represented properly.

有了这个方案，页面上的每个项目都有一个唯一的名称，并且可以正确地区分，并且每个操作都可以正确地表示。

When the form is posted, here's a sample of building one of the two recordsets used (classic ASP code):

当表单发布时，这里有一个构建使用的两个记录集之一的示例(经典的ASP代码):

Set Data = Server.CreateObject("ADODB.Recordset")
Data.Fields.Append "ElementID", adInteger, 4, adFldKeyColumn
Data.Fields.Append "AttrID", adInteger, 4, adFldKeyColumn
Data.Fields.Append "Value", adLongVarWChar, 2147483647, adFldIsNullable Or adFldMayBeNull
Data.CursorLocation = adUseClient
Data.CursorType = adOpenDynamic
Data.Open

This is the recordset for values, the other is for the elements themselves.

这是值的记录集，另一个是元素本身。

I step through the posted form and for the element recordset use a Scripting.Dictionary populated with instances of a custom Class that has the properties I need, so that I can add the values piecemeal, since they don't always come in order. New elements are added as negative to distinguish them from regular elements (rather than requiring a separate column to indicate if it is new or addresses an existing element). I use regular expression to tear apart the form keys: "^(e|n)([0-9]{1,10})_(a|p|t|c)([0-9]{0,10})$"

我遍历已发布的表单，对元素记录集使用脚本。Dictionary中有一个自定义类的实例，它具有我需要的属性，这样我就可以将这些值逐个添加，因为它们并不总是按顺序排列。新元素被添加为负值以区别于常规元素(而不是需要单独的列来指示它是新的还是针对现有元素)。我使用正则表达式来撕裂形成键:“^(e | n)([0 - 9]{ 1,10 })_(p t | | | c)([0 - 9]{ 0,10 })$”

Then, adding an attribute looks like this.

然后，添加属性如下所示。

Data.AddNew
ElementID.Value = DataID
AttrID.Value = Integerize(Matches(0).SubMatches(3))
AttrValue.Value = Request.Form(Key)
Data.Update

ElementID, AttrID, and AttrValue are references to the fields of the recordset. This method is hugely faster than using Data.Fields("ElementID").Value each time.

eld、AttrID和AttrValue是对记录集字段的引用。这个方法比使用Data.Fields(“eld”)要快得多。每次都值。

I loop through the Dictionary of element updates and ignore any that don't have all the proper information, adding the good ones to the recordset.

我循环遍历元素更新的字典，忽略任何没有所有适当信息的字典，将好的元素添加到记录集。

Then I call my data-updating stored procedure like so:

然后我调用我的数据更新存储过程如下:

Set Cmd = Server.CreateObject("ADODB.Command")
With Cmd
   Set .ActiveConnection = MyDBConn
   .CommandType = adCmdStoredProc
   .CommandText = "DataPost"
   .Prepared = False
   .Parameters.Append .CreateParameter("@ElementMetadata", adLongVarWChar, adParamInput, 2147483647, XMLFromRecordset(Element))
   .Parameters.Append .CreateParameter("@ElementData", adLongVarWChar, adParamInput, 2147483647, XMLFromRecordset(Data))
End With
Result.Open Cmd ' previously created recordset object with options set

Here's the function that does the xml conversion:

下面是执行xml转换的函数:

Private Function XMLFromRecordset(Recordset)
   Dim Stream
   Set Stream = Server.CreateObject("ADODB.Stream")
   Stream.Open
   Recordset.Save Stream, adPersistXML
   Stream.Position = 0
   XMLFromRecordset = Stream.ReadText
End Function

Just in case the web page needs to know, the SP returns a recordset of any new elements, showing their page value and their created value (so I can see that n1 is now e12346 for example).

为了防止web页面需要知道，SP返回任何新元素的记录集，显示它们的页面值和创建的值(因此我可以看到n1现在是e12346)。

Here are some key snippets from the stored procedure. Note this is SQL 2000 for now, though I'll be able to switch to 2005 soon:

下面是存储过程中的一些关键片段。注意这是SQL 2000，不过我很快就能切换到2005:

CREATE PROCEDURE [dbo].[DataPost]
   @ElementMetaData ntext,
   @ElementData ntext
AS
DECLARE @hdoc int

--- snip ---

EXEC sp_xml_preparedocument @hdoc OUTPUT, @ElementMetaData, '<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" />'
INSERT #ElementMetadata (ElementID, ElementTypeID, ElementID1, ElementID2)
SELECT *
FROM
   OPENXML(@hdoc, '/xml/rs:data/rs:insert/z:row', 0)
   WITH (
      ElementID int,
      ElementTypeID int,
      ElementID1 int,
      ElementID2 int
   )
ORDER BY ElementID -- orders negative items (new elements) first so they begin counting at 1 for later ID calculation

EXEC sp_xml_removedocument @hdoc

--- snip ---

UPDATE E
SET E.ElementTypeID = M.ElementTypeID
FROM
   Element E
   INNER JOIN #ElementMetadata M ON E.ElementID = M.ElementID
WHERE
   E.ElementID >= 1
   AND M.ElementTypeID >= 1

The following query does the correlation of the negative new element ids to the newly inserted ones:

下面的查询将负的新元素id与新插入的元素id进行关联:

UPDATE #ElementMetadata -- Correlate the new ElementIDs with the input rows
SET NewElementID = Scope_Identity() - @@RowCount + DataID
WHERE ElementID < 0

Other set-based queries do all the other work of validating that the attributes are allowed, are the correct data type, and inserting, updating, and deleting elements and attributes.

其他基于集合的查询执行所有其他工作，如验证允许的属性、正确的数据类型以及插入、更新和删除元素和属性。

I hope this brief run-down is useful to others some day! Converting ADO Recordsets to an XML stream was a huge winner for me as it saved all sorts of time and had a namespace and schema already defined that made the results come out correctly.

我希望这个简短的总结有一天对别人有用!将ADO记录集转换为XML流对我来说是一个巨大的成功，因为它节省了各种时间，并且已经定义了一个名称空间和模式，从而使结果得到正确的结果。

Using a flatter XML format with 2 inputs was also much easier than sticking to some ideal about having everything in a single XML stream.

使用带有两个输入的扁平XML格式也比坚持一个XML流中的所有内容要容易得多。

2 个解决方案

#1

If I understand correctly, you are interested in the pros and cons of using XML as a format of data between the database and the application (in this case, a web app).

如果我理解正确，您会对使用XML作为数据库和应用程序之间的数据格式(在本例中是web应用程序)的利弊感兴趣。

If you happen to have the entire data to be inserted/updated/deleted as a handy bag of data in your client, then actually sending it as XML makes sense. On simple reason is that this would allow for a single database round-trip to the server, and reducing round-trips is always a good think. But the most important advantage is that you can employ the holy-grail of database performance: set oriented processing. Using XML methods, specially nodes and value, combined with some moderate XPath-fu skills, and you can shred the entire XML parameter received from the application into relational sets, and use set oriented operations to do the database writes.

如果您碰巧在客户机中插入/更新/删除了整个数据，并将其作为一个方便的数据包，那么将其作为XML发送是有意义的。原因很简单，这将允许一个数据库往返到服务器，减少往返总是一个好主意。但是最重要的优点是您可以使用数据库性能的holy-grail:面向对象的处理。使用XML方法，特别是节点和值，结合一些适度的XPath-fu技巧，您可以将从应用程序接收的整个XML参数分解为关系集，并使用面向集的操作来执行数据库写入。

Take for instance the XML in your post, lets say that it was passed as an parameter named @x of type XML. You can shred that into an attributes to be merged into existing elements:

以您的文章中的XML为例，假设它作为XML类型的@x参数传递。您可以将其分解为将其合并到现有元素中的属性:

select x.value(N'@ID', N'int') as ID,
  x.value(N'.', N'varchar(max)') as [Value]
from  @x.nodes('//Element[not(@Action="delete") and not (@ID=0)]/Attr') t(x)

You can shred the attributes that go into new elements:

你可以将属性分解成新的元素:

select x.value(N'@ID', N'int') as ID,
  x.value(N'.', N'varchar(max)') as [Value]
from  @x.nodes('//Element[@ID=0]/Attr') t(x);

And you can shred the elements to be deleted:

可以将要删除的元素进行分解:

select x.value(N'@ID', N'int') as ID
from  @x.nodes('//Element[@Action="delete"]') t(x);

These sets can be manipulated via normal SQL DML: inserted, deleted, updated or merged into the EAV tables, in one single pass. Note that the XML shredding I show here are trivial ones and probably incorrect for you, but are just to show the way to do it.

这些集可以通过普通的SQL DML进行操作:一次插入、删除、更新或合并到EAV表中。请注意，我在这里展示的XML分解对于您来说是微不足道的，而且可能是不正确的，但这只是为了说明如何实现它。

Now whether this is the best path to go, I don't know. There are way too many variables and moving pieces and they lay mostly in your dev team skill set and existing code base. For sure, XML is a good format to call into the database for updating sets, but XML has its shortcomings too: is verbose and fat, is slower to parse than binary formats, and is actually quite difficult to fully grok by programmers: once you get past the sugar coat of '<' and '>', there a deep (and sometimes messy) layer of XPath, XQuery, namespaces, encodings, cdata and the rest.

我不知道这是不是最好的方法。有太多的变量和移动部分，它们主要集中在您的开发团队技能集和现有代码库中。当然,XML是一个很好的格式调用数据库更新集,但XML也有其缺点:是冗长和脂肪,比二进制格式解析放缓,实际上是由程序员很难完全欣赏:一旦你得到的糖衣的“<”和“>”,有深(有时混乱)层XPath,XQuery,名称空间,编码,cdata和休息。

I'd say go ahead, prototype, let us know how it goes...

我想说继续，原型，让我们知道它是如何进行的…

#2

I don't see any reason not to use XML columns in SQL Server 2005, and to do all your work via stored procedures.

我认为没有任何理由不使用SQL Server 2005中的XML列，并通过存储过程完成所有工作。

You probably don't have time to abstract your data access to hide the ugliness of the data model, so why not just access it as-is, using XML? You can use XQuery in SQL Server to do updates, queries, etc.

您可能没有时间抽象数据访问来隐藏数据模型的丑陋之处，那么为什么不使用XML直接访问数据模型呢?您可以使用SQL Server中的XQuery进行更新、查询等。

Now that I think of it, you might still put one layer of abstraction between the ASP pages and the database. That would allow you in the future to use XSLT to transform the structure of your XML into a format that will perform better in the database.

现在我想起来了，您仍然可以在ASP页面和数据库之间放置一个抽象层。这将允许您在将来使用XSLT将XML结构转换为在数据库中执行得更好的格式。

#1