我必须使用每个ID只做一行的模式将SQL转换为XML文件。该查询转换 ID 的"文档"，并将它们全部放入一行架构中。它应该使用节点"documentData"向现有ID行添加其他文档，而不是为每个文档创建新行
为测试弹出创建和插入语句：
CREATE TABLE person
(
birthDate varchar(255),
lastName varchar(255),
firstName varchar(255),
externalStudentId3 varchar(255),
externalStudentId2 varchar(255),
externalStudentId1 varchar(255),
socialSecurityNumber varchar(255),
documentRequirementStatusChangeOnDate datetime,
reason varchar(255),
scopeValue varchar(255),
documentScope varchar(255),
status varchar(255),
documentName varchar(255),
externalDocumentId varchar(255),
dateReviewed datetime,
dateReceived datetime,
dateCreated datetime,
documentRequirementExternalId varchar(255)
)
INSERT INTO person
VALUES ('19540309','Smith','Jon','100000166','3014925','3014925','999999999',NULL,NULL,NULL,'PERSON','UNSATISFIED','ApplicationUpdate','Application_Update','20190222','20190223','20190224','01309757'),
('19540309','Smith','Jon','100000166','3014925','3014925','999999999','20190220','Document added to wrong year requirement.','2020','AwardYear','SATISFIED','CertOne','Cert_one','20190221','20190220','20190220','01294555')
示例数据：
示例数据的 Excel 屏幕截图
birthDate lastName firstName externalStudentId3 externalStudentId2 externalStudentId1 socialSecurityNumber documentRequirementStatusChangeOnDate reason scopeValue documentScope status documentName externalDocumentId dateReviewed dateReceived dateCreated documentRequirementExternalId
1954-03-09-05:00 Smith Jon 100000166 3014925 3014925 999999999 NULL NULL NULL PERSON UNSATISFIED ApplicationUpdate Application_Update 2019-12-05T08:40:17.737-05:00 2019-12-05T08:40:17.737-05:00 2019-12-05T08:40:17.737-05:00 01-309757
1954-03-09-05:00 Smith Jon 100000166 3014925 3014925 999999999 2019-02-20T10:54:08.670-05:00
每个 ID 拉取多行的当前代码：
SELECT
DISTINCT
s.dateOfBirth as birthDate
,s.lastName
,s.firstName
,s.externalId3 as externalStudentId3
,s.externalId2 as externalStudentId2
,s.externalid1 as externalStudentId1
,s.socialSecurityNumber as socialSecurityNumber
,s.message as reason
,s.documentScopeCode as documentScope
,s.documentRequirementStatusCode as status
,s.name as documentName
,s.externalId as externalDocumentId
,s.revieweddate as dateReviewed
,s.receiveddate as dateReceived
,s.createddate as dateCreated
,s.documentRequirementExternalId as documentRequirementExternalId
FROM dbo.[person] s
FOR XML RAW ('student'), ROOT ('students');`
电流输出：
当前输出屏幕截图
<persons>
<person birthDate="1954-03-09-05:00" lastName="Smith" firstName="Jon" externalStudentId3="100000166"
externalStudentId2="3014925" externalStudentId1="3014925" socialSecurityNumber="999999999"
documentScope="PERSON" status="UNSATISFIED" documentName="ApplicationUpdate"
externalDocumentId="Application_Update" dateReceived="2019-12-05T08:40:17.737-05:00"
dateCreated="2019-12-05T08:40:17.737-05:00" documentRequirementExternalId="01-309757" />
<person birthDate="1954-03-09-05:00" lastName="Smith" firstName="Jon" externalStudentId3="100000166"
externalStudentId2="3014925" externalStudentId1="3014925" socialSecurityNumber="999999999"
documentRequirementStatusChangeOnDate="2019-02-20T10:54:08.670-05:00" reason="Document added to
wrong year requirement." scopeValue="2020" documentScope="AwardYear" status="SATISFIED"
documentName="CertOne" externalDocumentId="Cert_one" dateReviewed="2019-02-20T10:54:08.670-05:00"
dateReceived="2019-02-19T13:35:03.143-05:00" dateCreated="2019-02-15T14:19:02.417-05:00"
documentRequirementExternalId="01-294517" />
</person>
期望输出：
所需的输出屏幕截图
<?xml version='1.0' encoding='UTF-8'?>
<persons>
<person birthDate="1954-03-09-05:00" lastName="Smith" firstName="Jon" externalStudentId3="100000166" externalStudentId2="3014925" externalStudentId1="3014925" socialSecurityNumber="999999999"><documentData documentScope="PERSON" status="UNSATISFIED" documentName="ApplicationUpdate" externalDocumentId="Application_Update" dateReceived="2019-12- 05T08:40:17.737-05:00" dateCreated="2019-12-05T08:40:17.737-05:00" documentRequirementExternalId="01-309757" /><documentData documentRequirementStatusChangeOnDate="2019-02-20T10:54:08.670-05:00" reason="Document added to wrong year requirement." scopeValue="2020" documentScope="AwardYear" status="SATISFIED" documentName="CertOne" externalDocumentId="Cert_one" dateReviewed="2019-02-20T10:54:08.670-05:00" dateReceived="2019-02-19T13:35:03.143-05:00" dateCreated="2019-02-15T14:19:02.417-05:00" documentRequirementExternalId="01-294517" />
</person>
</persons>
看看吧。我用了很少的列来展示这个概念。您可以轻松地将其扩展为完整解决方案。
.SQL
USE tempdb;
GO
-- DDL and sample data population, start
DROP TABLE IF EXISTS dbo.person;
CREATE TABLE dbo.person
(
birthDate varchar(255),
lastName varchar(255),
firstName varchar(255),
externalStudentId3 varchar(255),
externalStudentId2 varchar(255),
externalStudentId1 varchar(255),
socialSecurityNumber varchar(255),
documentRequirementStatusChangeOnDate datetime,
reason varchar(255),
scopeValue varchar(255),
documentScope varchar(255),
status varchar(255),
documentName varchar(255),
externalDocumentId varchar(255),
dateReviewed datetime,
dateReceived datetime,
dateCreated datetime,
documentRequirementExternalId varchar(255)
);
INSERT INTO dbo.person
VALUES ('19540309','Smith','Jon','100000166','3014925','3014925','999999999',NULL,NULL,NULL,'PERSON','UNSATISFIED','ApplicationUpdate','Application_Update','20190222','20190223','20190224','01309757')
, ('19540309','Smith','Jon','100000166','3014925','3014925','999999999','20190220','Document added to wrong year requirement.','2020','AwardYear','SATISFIED','CertOne','Cert_one','20190221','20190220','20190220','01294555');
-- DDL and sample data population, end
;WITH rs AS
(
-- select columns pertaining to the person only
SELECT DISTINCT externalStudentId3
, firstName
, lastName
FROM dbo.person
)
SELECT p.externalStudentId3 AS [@id]
, p.firstName AS [@firstName]
, p.lastName AS [@lastName]
, (SELECT c.externalStudentId3 AS [@id]
,c.documentScope AS [@documentScope]
,c.documentName AS [@documentName]
FROM dbo.person AS c
WHERE c.externalStudentId3 = p.externalStudentId3
FOR XML PATH('documentData'), TYPE)
FROM rs AS p
FOR XML PATH('person'), ROOT('persons');
输出
<persons>
<person id="100000166" firstName="Jon" lastName="Smith">
<documentData id="100000166" documentScope="PERSON" documentName="ApplicationUpdate" />
<documentData id="100000166" documentScope="AwardYear" documentName="CertOne" />
</person>
</persons>
您可以使用 FOX XML PATH 来实现此目的。我无法测试代码，可能需要进行一些调整：
CREATE TABLE person
(
birthDate varchar(255),
lastName varchar(255),
firstName varchar(255),
externalStudentId3 varchar(255),
externalStudentId2 varchar(255),
externalStudentId1 varchar(255),
socialSecurityNumber varchar(255),
documentRequirementStatusChangeOnDate datetime,
reason varchar(255),
scopeValue varchar(255),
documentScope varchar(255),
status varchar(255),
documentName varchar(255),
externalDocumentId varchar(255),
dateReviewed datetime,
dateReceived datetime,
dateCreated datetime,
documentRequirementExternalId varchar(255)
)
INSERT INTO person
VALUES ('19540309','Smith','Jon','100000166','3014925','3014925','999999999',NULL,NULL,NULL,'PERSON','UNSATISFIED','ApplicationUpdate','Application_Update','20190222','20190223','20190224','01309757'),
('19540309','Smith','Jon','100000166','3014925','3014925','999999999','20190220','Document added to wrong year requirement.','2020','AwardYear','SATISFIED','CertOne','Cert_one','20190221','20190220','20190220','01294555')
;WITH cte_person(birthDate,lastName,firstName,externalStudentId3,externalStudentId2,externalStudentId1,socialSecurityNumber)
AS
(
SELECT DISTINCT
s.birthDate as birthDate
,s.lastName
,s.firstName
,s.externalStudentId3
,s.externalStudentId2
,s.externalStudentId1
,s.socialSecurityNumber as socialSecurityNumber
FROM dbo.[person] s
)
SELECT
s.birthDate as '@birthDate'
,s.lastName as '@lastName'
,s.firstName as '@firstName'
,s.externalStudentId3 as '@externalStudentId3'
,s.externalStudentId2 as '@externalStudentId2'
,s.externalStudentId1 as '@externalStudentId1'
,s.socialSecurityNumber as '@socialSecurityNumber',
(SELECT
d.documentScope as '@documentScope'
,d.reason as '@reason'
,d.status as '@status'
,d.documentName as '@documentName'
,d.externalDocumentId as '@externalDocumentId'
,d.dateReviewed as '@dateReviewed'
,d.dateReceived as '@dateReceived'
,d.dateCreated as '@dateCreated'
,d.documentRequirementExternalId as '@documentRequirementExternalId'
FROM dbo.[person] d
WHERE d.socialSecurityNumber=s.socialSecurityNumber
FOR XML PATH ('documentData'), TYPE)
FROM cte_person s
FOR XML PATH ('student'), ROOT ('students');
输出：
<students>
<student birthDate="19540309" lastName="Smith" firstName="Jon" externalStudentId3="100000166" externalStudentId2="3014925" externalStudentId1="3014925" socialSecurityNumber="999999999">
<documentData documentScope="PERSON" status="UNSATISFIED" documentName="ApplicationUpdate" externalDocumentId="Application_Update" dateReviewed="2019-02-22T00:00:00" dateReceived="2019-02-23T00:00:00" dateCreated="2019-02-24T00:00:00" documentRequirementExternalId="01309757" />
<documentData documentScope="AwardYear" reason="Document added to wrong year requirement." status="SATISFIED" documentName="CertOne" externalDocumentId="Cert_one" dateReviewed="2019-02-21T00:00:00" dateReceived="2019-02-20T00:00:00" dateCreated="2019-02-20T00:00:00" documentRequirementExternalId="01294555" />
</student>
</students>