T-SQL上的 XML查询

Question

如果有任何帮助，我将不胜感激。

下面是我的表格结构:

CREATE TABLE [dbo].[XML_EXAMPLE]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Account Code] [varchar](15) NULL,
[Description] [varchar](50) NULL,
[Analysis 1 Code] [varchar](15) NULL,
[Analysis 2 Code] [varchar](15) NULL,
[Analysis 3 Code] [varchar](15) NULL,
[Analysis 4 Code] [varchar](15) NULL,
[Analysis 5 Code] [varchar](15) NULL,
[Analysis 6 Code] [varchar](15) NULL,
[Analysis 7 Code] [varchar](15) NULL
) ON [PRIMARY]
GO

在这个表中，我要插入4行。两行分析1代码为1000，另外两行分析1代码为2000。

INSERT INTO [dbo].[XML_EXAMPLE] ([Analysis 1 Code])
VALUES ('1000'), ('1000'), ('2000'), ('2000')

我想从SQL中得到的是如下所示的XML格式-注意每个分析1代码都有自己的"文档";元素，如果你看CompCode。每个Document元素都有一个Header和Item(表中的行)。

<DocumentBatch>
<Document>
<Header>
<DocType>HR</DocType>
<CompCode>1000</CompCode>
<DocDate>20211018</DocDate>
<PostDate>20211018</PostDate>
<RefDocNo>Civica Cx Rents</RefDocNo>
<Text>Civica Cx Rents</Text>
</Header>
<Item>
<ItemNo>1</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>
<Item>
<ItemNo>2</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>
</Document>
<Document>
<Header>
<DocType>HR</DocType>
<CompCode>2000</CompCode>
<DocDate>20211018</DocDate>
<PostDate>20211018</PostDate>
<RefDocNo>Civica Cx Rents</RefDocNo>
<Text>Civica Cx Rents</Text>
</Header>
<Item>
<ItemNo>1</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>
<Item>
<ItemNo>2</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>
</Document>
</DocumentBatch>

我所写的代码只处理分析1代码中的一个。如果你要运行这段代码，你会发现它只会给我想要的一半。

SELECT
(SELECT 
(SELECT DISTINCT 
'HR' DocType, 
x.[Analysis 1 Code] CompCode, 
CONVERT(varchar(8), GETDATE(), 112) DocDate, 
CONVERT(varchar(8), GETDATE(), 112) PostDate, 
'Civica Cx Rents' RefDocNo, 
'Civica Cx Rents' Text
FROM 
XML_EXAMPLE x
WHERE 
x.[Analysis 1 Code] = '1000'
FOR XML path('Header'), TYPE),
(SELECT  
ROW_NUMBER() OVER (ORDER BY id) 'ItemNo',
'GIAccNo' 'GIAccNo',
'TaxCode' 'TaxCode',
'Amount' 'Amount',
'Currency' 'Currency',
'Text' 'Text',
'CostCenter' 'CostCenter',
'RefKey1' 'RefKey1',
'RefKey2' 'RefKey2',
'RefKey3' 'RefKey3'
FROM 
XML_EXAMPLE x
WHERE 
x.[Analysis 1 Code] = '1000'
FOR XML PATH('Item'), TYPE)
FOR XML PATH('Document'), TYPE, ROOT ('DocumentBatch')) row

我的结果-注意，我只有一个文档元素的头和项目为1000。

<DocumentBatch>
<Document>
<Header>
<DocType>HR</DocType>
<CompCode>1000</CompCode>
<DocDate>20211018</DocDate>
<PostDate>20211018</PostDate>
<RefDocNo>Civica Cx Rents</RefDocNo>
<Text>Civica Cx Rents</Text>
</Header>
<Item>
<ItemNo>1</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>
<Item>
<ItemNo>2</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>
</Document>
</DocumentBatch>

我这辈子都做不出来的是——

1. 如何将<?xml version="1.0" encoding="iso8859-1"?>附加到我生成的XML格式的顶部-因为我将通过SSIS包设置它。

2. 在我的代码中，我把它放在哪里，以便它最终导出1000和2000，因为我正在努力把它放在哪里。我是否为了实现我想要的而错误地构造了我的代码?

多谢

Answer 1

请尝试以下解决方案。

使用SQL Server的XQuery和FLWOR表达式。

XML组合分两步完成:

1. 原始XML通过FOR XML PATH(...).
2. 通过XQuery FLWOR表达式最终微调XML。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE
(
ID int IDENTITY PRIMARY KEY,
Account_Code varchar(15) NULL,
[Description] varchar(50) NULL,
Analysis_1_Code varchar(15) NULL
);
INSERT INTO @tbl (Analysis_1_Code) VALUES 
('1000'), ('1000'), ('2000'), ('2000');
-- DDL and sample data population, end
SELECT (
SELECT * 
, seq = ROW_NUMBER() OVER (PARTITION BY Analysis_1_Code ORDER BY ID)
FROM @tbl
FOR XML PATH('r'), TYPE, ROOT('root'))
.query('<DocumentBatch>
{
for $x in distinct-values(/root/r/Analysis_1_Code)
return (<Document>
<Header>
<DocType>HR</DocType>
<CompCode>{$x}</CompCode>
<DocDate>20211018</DocDate>
<PostDate>20211018</PostDate>
<RefDocNo>Civica Cx Rents</RefDocNo>
<Text>Civica Cx Rents</Text>
</Header>
</Document>,
for $y in /root/r[Analysis_1_Code=$x]
return <Item>
<ItemNo>{data($y/seq)}</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>)
}
</DocumentBatch>');

<DocumentBatch>
<Document>
<Header>
<DocType>HR</DocType>
<CompCode>1000</CompCode>
<DocDate>20211018</DocDate>
<PostDate>20211018</PostDate>
<RefDocNo>Civica Cx Rents</RefDocNo>
<Text>Civica Cx Rents</Text>
</Header>
</Document>
<Item>
<ItemNo>1</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>
<Item>
<ItemNo>2</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>
<Document>
<Header>
<DocType>HR</DocType>
<CompCode>2000</CompCode>
<DocDate>20211018</DocDate>
<PostDate>20211018</PostDate>
<RefDocNo>Civica Cx Rents</RefDocNo>
<Text>Civica Cx Rents</Text>
</Header>
</Document>
<Item>
<ItemNo>1</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>
<Item>
<ItemNo>2</ItemNo>
<GIAccNo>GIAccNo</GIAccNo>
<TaxCode>TaxCode</TaxCode>
<Amount>Amount</Amount>
<Currency>Currency</Currency>
<Text>Text</Text>
<CostCenter>CostCenter</CostCenter>
<RefKey1>RefKey1</RefKey1>
<RefKey2>RefKey2</RefKey2>
<RefKey3>RefKey3</RefKey3>
</Item>
</DocumentBatch>