CASE SELECT..FOR XML内 - 避免多个元素
问题描述:
如何避免创建多个<Debtor>
和<Creditor>
元素。 下面是一个查询我需要提高。基本上,我需要得到双方<name>
和<address>
无论是<Creditor>
或<Debtor>
内取决于由CASE
条件表示Amt
。CASE SELECT..FOR XML内 - 避免多个元素
SELECT
(SELECT [Amt/@Curr] = t.Curr
,t.Amt
,[EntryDetails/TxnDetails/PostClassDt] = CASE WHEN Amt < 0 THEN 'debit' END
,[EntryDetails/TxnDetails/PostClassCr] = CASE WHEN Amt >= 0 THEN 'return' END
,[EntryDetails/TxnDetails/Parties/Creditor/Name] = CASE WHEN Amt < 0 THEN Contractor END
,[EntryDetails/TxnDetails/Parties/Debtor/Name] = CASE WHEN Amt >= 0 THEN Contractor END
,[EntryDetails/TxnDetails/Parties/Creditor/Address] = CASE WHEN Amt < 0 THEN [Address] END
,[EntryDetails/TxnDetails/Parties/Debtor/Address] = CASE WHEN Amt >= 0 THEN [Address] END
FROM (VALUES
('EUR', -123.45, 'John Doe','St.John'),
('USD', 456.78, 'Jane Doe', 'St.Jane')
) t (Curr, Amt, Contractor, [Address])
FOR XML PATH('Entry'), TYPE
) [Statement]
FOR XML PATH('Schema'), ROOT('Document')
需要的格式如下所示。任何帮助,好吗?
<Document>
<Schema>
<Statement>
<Entry>
<Amt Curr="EUR">-123.45</Amt>
<EntryDetails>
<TxnDetails>
<PostClassDt>debit</PostClassDt>
<Parties>
<Creditor>
<Name>John Doe</Name>
<Address>St.John</Address>
</Creditor>
</Parties>
</TxnDetails>
</EntryDetails>
</Entry>
<Entry>
<Amt Curr="USD">456.78</Amt>
<EntryDetails>
<TxnDetails>
<PostClassCr>return</PostClassCr>
<Parties>
<Debtor>
<Name>Jane Doe</Name>
<Address>St.Jane</Address>
</Debtor>
</Parties>
</TxnDetails>
</EntryDetails>
</Entry>
</Statement>
</Schema>
</Document>
答
更改您的选择顺序:
SELECT
(SELECT [Amt/@Curr] = t.Curr
,t.Amt
,[EntryDetails/TxnDetails/PostClassDt] = CASE WHEN Amt < 0 THEN 'debit' END
,[EntryDetails/TxnDetails/PostClassCr] = CASE WHEN Amt >= 0 THEN 'return' END
,[EntryDetails/TxnDetails/Parties/Creditor/Name] = CASE WHEN Amt < 0 THEN Contractor END
,[EntryDetails/TxnDetails/Parties/Creditor/Address] = CASE WHEN Amt < 0 THEN [Address] END
,[EntryDetails/TxnDetails/Parties/Debtor/Name] = CASE WHEN Amt >= 0 THEN Contractor END
,[EntryDetails/TxnDetails/Parties/Debtor/Address] = CASE WHEN Amt >= 0 THEN [Address] END
FROM (VALUES
('EUR', -123.45, 'John Doe','St.John'),
('USD', 456.78, 'Jane Doe', 'St.Jane')
) t (Curr, Amt, Contractor, [Address])
FOR XML PATH('Entry'), TYPE
) [Statement]
FOR XML PATH('Schema'), ROOT('Document')