查看以下示例并对其进行修改以供您自己使用。它们都作为输入而基于发票消息:
在此示例中,发票包含可变数目的项。转换如下所示:
SET OutputRoot.XML.Data.Statement[] = (SELECT I.Customer.Title AS Customer.Title, I.Customer.FirstName || ' ' || I.Customer.LastName AS Customer.Name, COALESCE(I.Customer.PhoneHome,'') AS Customer.Phone, (SELECT II.Title AS Desc, CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost, II.Quantity AS Qty FROM I.Purchases.Item[] AS II WHERE II.UnitPrice > 0.0 ) AS Purchases.Article[], (SELECT SUM( CAST(II.UnitPrice AS FLOAT) * CAST(II.Quantity AS FLOAT) * 1.6 ) FROM I.Purchases.Item[] AS II ) AS Amount, 'Dollars' AS Amount.(XML.Attribute)Currency FROM InputRoot.XML.Invoice[] AS I WHERE I.Customer.LastName <> 'Brown' );
生成的输出消息是:
<Data> <Statement> <Customer> <Title>Mr</Title> <Name>Andrew Smith</Name> <Phone>01962818000</Phone> </Customer> <Purchases> <Article> <Desc Category="Computer" Form="Paperback" Edition="2">The XML Companion</Desc> <Cost>4.472E+1</Cost> <Qty>2</Qty> </Article> <Article> <Desc Category="Computer" Form="Paperback" Edition="2"> A Complete Guide to DB2 Universal Database</Desc> <Cost>6.872E+1</Cost> <Qty>1</Qty> </Article> <Article> <Desc Category="Computer" Form="Hardcover" Edition="0">JAVA 2 Developers Handbook</Desc> <Cost>9.5984E+1</Cost> <Qty>1</Qty> </Article> </Purchases> <Amount Currency="Dollars">2.54144E+2</Amount> </Statement> </Data>
此转换有两个彼此嵌入的 SELECT。外部的 SELECT 处理发票列表。内部的 SELECT 处理项列表。与内部 SELECT 关联的 AS 子句期望一个数组:
(SELECT II.Title AS Desc, CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost, II.Quantity AS Qty FROM I.Purchases.Item[] AS II WHERE II.UnitPrice > 0.0 ) -- Note the use of [] in the next expression AS Purchases.Article[],
这告诉外部的 select 期望在每个结果中有可变数目的项。每个 SELECT 具有自己的相关名称:I 表示外部的 select,II 表示内部的 select。每个 SELECT 通常使用自己的相关名称,但内部 SELECT 的 FROM 子句引用外部 SELECT 的相关名称:
(SELECT II.Title AS Desc, CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost, II.Quantity AS Qty -- Note the use of I.Purchases.Item in the next expression FROM I.Purchases.Item[] AS II WHERE II.UnitPrice > 0.0 ) AS Purchases.Article[],
这告诉内部的 SELECT 处理当前发票项。两个 SELECT 都包含 WHERE 子句。外部 SELECT 使用一个标准废弃特定客户,内部的 SELECT 使用不同的标准废弃特定项。 此示例还显示 COALESCE 的使用以阻止缺少的输入字段导致缺少相关输出字段。最后,它还使用列函数 SUM 一起将所有项的值添加到每个发票中。列函数在引用数据库中的列中讨论。
当创建字段 Desc 时,复制整个输入标题字段:XML 属性和字段值。 如果您希望在输出消息中没有这些属性,则可以使用 FIELDVALUE 函数来丢弃它们,如下列 ESQL 的示例代码:
SET OutputRoot.XML.Data.Statement[] = (SELECT I.Customer.Title AS Customer.Title, I.Customer.FirstName || ' ' || I.Customer.LastName AS Customer.Name, COALESCE(I.Customer.PhoneHome,'') AS Customer.Phone, (SELECT FIELDVALUE(II.Title) AS Desc, CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost, II.Quantity AS Qty FROM I.Purchases.Item[] AS II WHERE II.UnitPrice > 0.0 ) AS Purchases.Article[], (SELECT SUM( CAST(II.UnitPrice AS FLOAT) * CAST(II.Quantity AS FLOAT) * 1.6 ) FROM I.Purchases.Item[] AS II ) AS Amount, 'Dollars' AS Amount.(XML.Attribute)Currency FROM InputRoot.XML.Invoice[] AS I WHERE I.Customer.LastName <> 'Brown' );
这生成以下输出消息:
<Data> <Statement> <Customer> <Title>Mr</Title> <Name>Andrew Smith</Name> <Phone>01962818000</Phone> </Customer> <Purchases> <Article> <Desc>The XML Companion</Desc> <Cost>4.472E+1</Cost> <Qty>2</Qty> </Article> <Article> <Desc>A Complete Guide to DB2 Universal Database</Desc> <Cost>6.872E+1</Cost> <Qty>1</Qty> </Article> <Article> <Desc>JAVA 2 Developers Handbook</Desc> <Cost>9.5984E+1</Cost> <Qty>1</Qty> </Article> </Purchases> <Amount Currency="Dollars">2.54144E+2</Amount> </Statement> </Data>