SQL Server 高级设置 - 知行之桥文档

XML 模型

SQL Server 端口将知行之桥中的 SQL Server 表建模为 XML。建模遵循以下结构：

父元素标识要插入的表。
每个子元素对应目标表中的一列。

这在 XML 元素和 SQL Server 值之间建立了连接，这允许端口从传入的 XML 文档中读取值并在 INSERT 或 UPDATE 查询中使用它们。单击配置面板上方的 </> 代码 可以查看表的 XML 模型。以下 XML 是名为 Account 的表的简化示例模型，它被建模为 XML 元素 <Account>：

<Items>
    <Account action="upsert">
        <Id key="true" />
        <account_c />
        <AccountNumber />
        <AccountSource />
    </Account>
</Items>

在上面的例子中，Id、account_c、AccountNumber 和 AccountSource 是 Account 表中的列。当端口处理与此结构匹配的 XML 文件时，它会自动将来自该 XML 的值插入到 Account 表中。当端口查询 Account 表时，它会输出与此结构匹配的 XML。

批量输入

批量输出

查询输出时，可以配置端口以支持将记录批处理到单个文档中。Max Records 设置控制单个消息中应包含多少条记录，Batch Size 设置指示单个批处理组中应包含多少条批处理消息。

子表

子表 XML

插入父表和子表时，输入映射 XML 结构将是多层的。以下 XML 是通过外键关联的两个表的示例输入映射：

<Account action="upsert">
    <Id key="true" />
    <account_c />
    <AccountNumber />
    <AccountSource />
    <AccountPartner>
        <Id key="true" />
        <AccountFromId ref="@Id" />
        <IsPrimary />
        <OpportunityId />
        <Role />
    </AccountPartner>
</Account>

在上面的例子中，Account 是父表，AccountPartner 是子表。当 SQL Server 端口处理匹配此结构的输入 XML 文件时，它会更新这两个表并确保链接两条记录的键相同。

使用 ref 属性

子表中的某些列标有 ref 标记。此标记表示这些列对应于父表中的主键。在对父表进行插入之后，在对子表进行插入之前，端口会在父表中查询它刚刚插入的记录的主键。然后，端口在插入子表时使用此值。当父表的主键由 SQL Server 生成时，这很有用。在这种情况下，此列不会作为输入 XML 传入，因此端口在将记录插入到父表中之前不知道该列的值。在 XML 模型中，ref 属性表示可以稍后引用列，或者应该从映射中的另一列读取列的值。为了引用由 SQL Server 生成的列的值，列元素应包含一个设置为保留值 LAST_INSERT_ID 的 ref 属性：

<Item action="upsert">
    <Id key="true" upsert="id" ref="@@LAST_INSERT_ID" />

这表明 id 值将在插入后检索，并且可以在以后引用（通常在子表中）。要稍后引用此值，请将 ref 元素设置为相关列的名称：

<id key="true" ref="@id" />

在上面的例子中，id 是父表中主键的名称，也是子表中某列的名称。ref 语法指示端口在插入后从父表中检索 id，然后将检索到的值用作子表中的 id 列。

从子表中查询

通常，当从父表和子表中选择时，如果外键与父表中的主键匹配，则应仅从子表中提取记录。

例如，假设一个 Select 从父表 PurchaseOrders 和子表 PurchaseOrderLineItems 中提取采购订单数据。从 PurchaseOrders 拉取的每条记录（订单）还应拉取与该订单关联的行项目。这两个表通过共享的 PONumber 列关联，该列是 PurchaseOrders 表的主键和 PurchaseOrdersLineItems 表中的外键。如果订单项的 PONumber 与从父表中提取的订单的 PONumber 匹配，则应仅从子表中提取订单项记录。

要在知行之桥中建立这种关系，请向 Select 添加一个新过滤器并按如下方式配置它：

在第一个（左侧）下拉列表中，选择作为父表外键的子表列。在上面的示例中，这将是 PurchaseOrderLineItems 表的 PONumber 列。
在中间（运算符）下拉列表中，选择 equal。
在最后一个（右侧）输入框中，首先使用右侧的下拉箭头选择 REF。之后，输入框应该有 $ 前缀，表示正在引用父表。
使用最后一个（右侧）下拉列表选择应与子表的外键匹配的父表列。在上面的示例中，这将是 PurchaseOrders 表的 PONumber 列。

仅处理新记录或已更改的记录

可以将选择操作配置为仅检索最近更新或添加的记录。端口可以使用两种方法来检测哪些记录是新的或被修改的。

第一种方法需要一个 DateTime 列来表示记录的最后修改时间。
第二种方法需要一个列来显式存储一个值，该值表示是否应该处理记录。

这两个选项均在配置面板底部的高级设置中的设置选项卡上设置。要进一步细化您的要求，请参阅高级选项卡的高级设置部分中的三个附加 流程更改... 设置。

使用 Last Modified 列

在配置面板底部找到 高级设置 设置。如果检测到 DateTime 列，则选项显示为 使用列处理新的记录或已更改的记录。将此设置为表示记录的上次修改时间的相应 DateTime 列。

使用自定义列

预处理和后处理查询

在 Upsert 映射中

在 UPSERT 映射中，可以发出在所有记录被 UPSERT 到表中之前或之后自动执行的查询。要配置此功能，首先单击 Upsert 配置 窗格中的 </> 代码。

打开的文本编辑器允许编辑数据表映射的高级功能。文本可能如下所示：

<Items>
    <Names table="`dbo`.`Names`" action="upsert">
        <ID key="true" />
        <Name />
        …
    </Names>
</Items>

如要在任何列被添加到表之前或之后触发请求，定义一个具有以下属性的节点：

requireInput：设置为 false。此属性值指示端口即使输入文件中没有任何匹配的输入元素也发出查询。
insertQuery：包含要在 UPSERT 查询之前或之后执行的语句。
table（可选）：设置为要使用额外查询定位的表的名称。（在下面的示例中，表名称是 updateTable。）如果在映射的根级别发出查询（即，作为 <Items> 元素的子元素），此属性不是必需的。如果将查询作为另一个表的子项发出，设置 table 属性可确保查询与该表的列区分开来。

根据你希望它执行的时间，将新定义的元素放在 UPSERT 查询之前或之后。在下面的例子中，<runFirst> 会在任何 UPSERT 请求之前对表运行 EXEC [dbo.FlushTemp]：

<Items>
    <runFirst table="updateTable" insertQuery="EXEC [dbo].[FlushTemp]" requireInput="false" />
    <Names table="`dbo`.`Names`" action="upsert">
        <ID key="true" />
        <Name />
        …
    </Names>
</Items>

在下面的例子中，<runLast> 会在所有 UPSERT 请求完成后对表运行 EXEC [dbo].[CommitTemp]。

<Items>
    <Names table="`dbo`.`Names`" action="upsert">
        <ID key="true" />
        <Name />
        …
    </Names>
    <runLast table="updateTable" insertQuery="EXEC [dbo].[CommitTemp]" requireInput="false" />
</Items>

例子中的元素名称（runFirst 和 runLast）都是占位符，你可以随意自选。

UPSERT 映射中的预处理和后处理查询不接受查询中未硬编码的输入元素，并且它们也不会生成输出数据。

在 Select 映射中

在 Select 映射中，可以发出在对目标表发出查询之前或之后自动执行的查询。要配置此功能，首先单击 Select 配置 窗格中的 </> 代码。

打开的文本编辑器允许编辑数据表映射的高级功能。如要在任何列被添加到表之前或之后触发请求，定义一个具有以下属性的节点：

outputResult：设置为 false。此属性值指示端口不将查询的任何数据作为结果返回到输出文件中。
selectQuery：包含要在 SELECT 查询之前或之后执行的语句。
table（可选）：设置为要使用额外查询定位的表的名称。（在下面的示例中，表名称是 selectTable。）如果在映射的根级别发出查询（即，作为 <Items> 元素的子元素），此属性不是必需的。如果将查询作为另一个表的子项发出，设置 table 属性可确保查询与该表的列区分开来。

根据你希望它执行的时间，将新定义的元素放在 SELECT 查询之前或之后。在下面的例子中，<runFirst> 会在对表发出 SELECT 查询之前运行 EXEC [dbo].[prepTable]：

<Items>
    <runFirst table="selectTable" selectQuery="EXEC [dbo].[prepTable]" outputResult="false" />
    <Names table="`dbo`.`Names`" action="select">
        <ID key="true" />
        <Name />
        …
    </Names>
</Items>

在下面的例子中，<runLast> 会在对表发出 SELECT 查询及任何子查询之后运行 EXEC [dbo].[cleanTable]：

<Items>
    <Names table="`dbo`.`Names`" action="select">
        <ID key="true" />
        <Name />
        …
    </Names>
    <runLast table="selectTable" selectQuery="EXEC [dbo].[cleanTable]" outputResult="false" />
</Items>

例子中的元素名称（runFirst 和 runLast）都是占位符，你可以随意自选。

SELECT 映射中的预处理和后处理查询不接受查询中未硬编码的输入元素。

XML 属性

表属性

allColumns

在选择（Select）映射中，使用此属性指定输出 XML 应包含查询结果中返回的所有列。这允许定义输出映射，而无需在输出映射中显式定义所有列的名称。如果不使用此属性，输出映射中存在的列将用于按照代码视图中指定的顺序对查询返回的元素进行定位，查询中返回但输出映射中没有匹配列的元素将不会返回到输出 XML 中。

<MyTable allcolumns="true" selectQuery="SELECT * FROM [MyTable]" />

insertQuery

在更新/插入（Upsert）映射中，当插入记录时，可以使用此查询覆盖端口创建的默认查询。它可以用于发布超出与输入文件关联的普通插入操作之外的查询。

<runFirst insertQuery="TRUNCATE TABLE [dbo].[Records]" requireInput="false" />

outputResult

在选择（Select）映射中，如果设置此属性，则会发出匹配 Select 查询的查询，但不会生成要在输出 XML 中返回的内容。这允许运行作为事务一部分的其他查询，而这些查询不会反映在端口的输出中。

<runFirst table="selectTable" selectQuery="EXEC [dbo].[prepTable]" outputResult="false" />

processChangesOnly

在选择（Select）映射中，如果将 processChangesOnly 属性应用于表，并结合将 timeCheck 属性应用于日期时间列，则选择映射会在查询中动态生成的 WHERE 子句中使用此列，以确保仅检索新记录。这对应于设计器中 使用列 columnname 处理新记录或更改记录 选项中所选的列。有关更多信息，请参阅仅处理新记录或更改记录。

此属性仅适用于选择映射中的根表。

<Orders processChangesOnly="true">

requireInput

在更新/插入（Upsert）映射中，如果设置此属性，则无论输入 XML 是否包含匹配元素，都会执行与 inputQuery 匹配的查询。当预处理或后处理查询作为存在一个或多个输入元素的同一事务的一部分发布时（例如，在输入新批次之前截断表，或在批次完成后运行存储过程），将使用此属性。

<runFirst insertQuery="TRUNCATE TABLE [dbo].[Records]" requireInput="false" />

selectQuery

在选择（Select）映射中，此属性用于在 XML 中定义查询及其参数。如果未提供 selectQuery，则会发出简单的 SELECT *。可以将 selectQuery 用于自定义查询，例如使用 JOIN 或其他 SQL 关键字的查询。

<Orders selectQuery="SELECT ID, OrderNo, OrderDate FROM Orders">

table

用于指定作为映射查询目标的表，特别是当表名与映射到它的有效 XML 元素名称不同时。

如果在 selectQuery、upsertQuery 或 insertQuery 中编写了显式查询，则此属性将被忽略。

<Purchase_Orders table="Purchase Orders">

upsertQuery

在更新/插入（Upsert）映射中，使用此查询来确定正在处理的记录是新记录（INSERT）还是正在更新的现有记录（UPDATE）。操作将根据是否找到此查询的匹配结果来确定。

<Records action="upsert" upsertQuery="SELECT ID FROM Records WHERE Key1 = @Key1 and Key2 = @Key2">

列属性

aggregate

在某些情况下，映射中列的值本身可能是一个 XML 聚合。默认行为是将该列视为字符串，并使用 XML 编码对列中聚合的值进行编码。但是，当列上存在 aggregate 属性时，映射到该表的 XML 文件会将任何聚合列的值作为原始 XML，而不是编码为单个元素。

<LineItems aggregate="true" />

column

映射中列上的 column 属性提供了所引用表中的列名。这可以实现以下几点：

映射到非有效 XML 元素的列
为数据库中的列创建别名，以将其与其他表中名称相似的列区分开来
在 XML 模型中使用更易读的元素

<New_data column="New Data" />

id

在选择（Select）映射中，此属性使用 Id 标记对列值进行标记，随后可以在子查询中使用语法 ${#ID} 引用该标记。当子查询中需要列值，且多个表中的匹配列名可能导致选择歧义时，这非常有用。

    <Shipments table="`Shipments`" selectQuery="SELECT * FROM `Shipments`">
        <ID type="integer" key="true" id="ShipID"/>
        ...
        <ShipmentOrders table="`ShipmentOrders`" selectQuery="SELECT * FROM `ShipmentOrders` WHERE `ShipmentID` = ${ID}">
            <ID type="integer" key="true" id="ShipOrderID"/>
            ...
            <ShipmentOrderItems table="`ShipmentOrderItems`" selectQuery="SELECT * FROM `ShipmentOrderItems` WHERE `ShipmentOrderID` = ${ID}">
                <ID type="integer" key="true" id="ShipOrderItemID"/>
                ...

key

将该列标识为表的主键。当此属性出现在更新/插入（Upsert）查询中时，端口会在更新记录前检查该记录是否存在。如果表中没有主键属性，则仅允许对表进行插入（Insert）操作，且在选择记录后无法更新列值，因为无法标识唯一记录。（如果确定某列可以像主键列一样使用，则可以覆盖现有列作为主键。）

<ID key="true" type="integer"/>

ref

在更新/插入（Upsert）映射中，此属性用于向数据库提供无法仅从输入 XML 中确定的更新/插入值。这最常用于父子关系中以确定数据库生成的主键，或者用于一个表引用子表上的外键。有关更多信息，请参阅使用 ref 属性。

   <Orders table="`Orders`" action="upsert">
        <ID type="integer" key="true" ref="@@LAST_INSERT_ID" />
        ...
        <OrderLines table="`OrderLines`" action="upsert">
            <ID type="integer" key="true" />
            ...
            <OrderID type="integer" ref="@ID" />
        </OrderLines>
    </Orders>

timeCheck

此属性仅适用于选择映射中的根表。

<LastModified timeCheck="true" />

type

用于标识表中列的数据类型。对于更新/插入（Upsert）查询，在执行查询之前，系统会根据该类型对列中的值进行验证。

<ID key="true" type="integer"/>

updateValue

在选择（Select）映射中，从数据库中选择的任何记录在检索后，都会使用此列中的新值进行更新。这对应于设计器中的当行处理成功时，使用值 value 更新列 columnname 选项。

此属性仅适用于查询中的根表。

<Exported updateValue="true" />

​XML 模型

​批量输入

​批量输出

​子表

​子表 XML

​使用 ref 属性

​从子表中查询

​仅处理新记录或已更改的记录

​使用 Last Modified 列

​使用自定义列

​预处理和后处理查询

​在 Upsert 映射中

​在 Select 映射中

​XML 属性

​表属性

​allColumns

​insertQuery

​outputResult

​processChangesOnly

​requireInput

​selectQuery

​table

​upsertQuery

​列属性

​aggregate

​column

​id

​key

​ref

​timeCheck

​type

​updateValue

XML 模型

批量输入

批量输出

子表

子表 XML

使用 ref 属性

从子表中查询

仅处理新记录或已更改的记录

使用 Last Modified 列

使用自定义列

预处理和后处理查询

在 Upsert 映射中

在 Select 映射中

XML 属性

表属性

allColumns

insertQuery

outputResult

processChangesOnly

requireInput

selectQuery

table

upsertQuery

列属性

aggregate

column

id

key

ref

timeCheck

type

updateValue