将SQL转换为每个ID一行的XML架构（FOR XML RAW）的方法是：

huangapple
go评论145阅读模式
英文:

How to convert SQL into XML schema of one row per ID (FOR XML RAW)

问题

以下是翻译好的部分：

<?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>
英文:

I have to convert SQL into an XML file using a schema that only makes one row per ID. The query converts an ID's "documents" and puts them all into a one row schema. Instead of making a new row for each document it should add additional documents to the existing ID row using the node "documentData"

Create and INSERT statements for test pop:

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 (&#39;19540309&#39;,&#39;Smith&#39;,&#39;Jon&#39;,&#39;100000166&#39;,&#39;3014925&#39;,&#39;3014925&#39;,&#39;999999999&#39;,NULL,NULL,NULL,&#39;PERSON&#39;,&#39;UNSATISFIED&#39;,&#39;ApplicationUpdate&#39;,&#39;Application_Update&#39;,&#39;20190222&#39;,&#39;20190223&#39;,&#39;20190224&#39;,&#39;01309757&#39;),
(&#39;19540309&#39;,&#39;Smith&#39;,&#39;Jon&#39;,&#39;100000166&#39;,&#39;3014925&#39;,&#39;3014925&#39;,&#39;999999999&#39;,&#39;20190220&#39;,&#39;Document added to wrong year requirement.&#39;,&#39;2020&#39;,&#39;AwardYear&#39;,&#39;SATISFIED&#39;,&#39;CertOne&#39;,&#39;Cert_one&#39;,&#39;20190221&#39;,&#39;20190220&#39;,&#39;20190220&#39;,&#39;01294555&#39;)

Sample Data:

excel screenshot of sample data

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

Current Code that pulls multiple rows per 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 (&#39;student&#39;), ROOT (&#39;students&#39;);`

Current Output:

current output screenshot

&lt;persons&gt;
&lt;person birthDate=&quot;1954-03-09-05:00&quot; lastName=&quot;Smith&quot; firstName=&quot;Jon&quot; externalStudentId3=&quot;100000166&quot; 
externalStudentId2=&quot;3014925&quot; externalStudentId1=&quot;3014925&quot; socialSecurityNumber=&quot;999999999&quot; 
documentScope=&quot;PERSON&quot; status=&quot;UNSATISFIED&quot; documentName=&quot;ApplicationUpdate&quot; 
externalDocumentId=&quot;Application_Update&quot; dateReceived=&quot;2019-12-05T08:40:17.737-05:00&quot; 
dateCreated=&quot;2019-12-05T08:40:17.737-05:00&quot; documentRequirementExternalId=&quot;01-309757&quot; /&gt;
&lt;person birthDate=&quot;1954-03-09-05:00&quot; lastName=&quot;Smith&quot; firstName=&quot;Jon&quot; externalStudentId3=&quot;100000166&quot; 
externalStudentId2=&quot;3014925&quot; externalStudentId1=&quot;3014925&quot; socialSecurityNumber=&quot;999999999&quot; 
documentRequirementStatusChangeOnDate=&quot;2019-02-20T10:54:08.670-05:00&quot; reason=&quot;Document added to 
wrong year requirement.&quot; scopeValue=&quot;2020&quot; documentScope=&quot;AwardYear&quot; status=&quot;SATISFIED&quot; 
documentName=&quot;CertOne&quot; externalDocumentId=&quot;Cert_one&quot; dateReviewed=&quot;2019-02-20T10:54:08.670-05:00&quot; 
dateReceived=&quot;2019-02-19T13:35:03.143-05:00&quot; dateCreated=&quot;2019-02-15T14:19:02.417-05:00&quot; 
documentRequirementExternalId=&quot;01-294517&quot; /&gt;
&lt;/person&gt;

Desired Output:

desired output screenshot

&lt;?xml version=&#39;1.0&#39; encoding=&#39;UTF-8&#39;?&gt;
&lt;persons&gt;
  &lt;person birthDate=&quot;1954-03-09-05:00&quot; lastName=&quot;Smith&quot; firstName=&quot;Jon&quot; externalStudentId3=&quot;100000166&quot; externalStudentId2=&quot;3014925&quot; externalStudentId1=&quot;3014925&quot; socialSecurityNumber=&quot;999999999&quot;&gt;&lt;documentData documentScope=&quot;PERSON&quot; status=&quot;UNSATISFIED&quot; documentName=&quot;ApplicationUpdate&quot; externalDocumentId=&quot;Application_Update&quot; dateReceived=&quot;2019-12-  05T08:40:17.737-05:00&quot; dateCreated=&quot;2019-12-05T08:40:17.737-05:00&quot; documentRequirementExternalId=&quot;01-309757&quot; /&gt;&lt;documentData documentRequirementStatusChangeOnDate=&quot;2019-02-20T10:54:08.670-05:00&quot; reason=&quot;Document added to  wrong year requirement.&quot; scopeValue=&quot;2020&quot; documentScope=&quot;AwardYear&quot; status=&quot;SATISFIED&quot; documentName=&quot;CertOne&quot; externalDocumentId=&quot;Cert_one&quot; dateReviewed=&quot;2019-02-20T10:54:08.670-05:00&quot; dateReceived=&quot;2019-02-19T13:35:03.143-05:00&quot; dateCreated=&quot;2019-02-15T14:19:02.417-05:00&quot; documentRequirementExternalId=&quot;01-294517&quot; /&gt;
  &lt;/person&gt;
&lt;/persons&gt;

答案1

得分: 0

你可以使用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>
英文:

You can achieve this using FOX XML PATH. I can't test the code, some tweak may needed:

 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 (&#39;19540309&#39;,&#39;Smith&#39;,&#39;Jon&#39;,&#39;100000166&#39;,&#39;3014925&#39;,&#39;3014925&#39;,&#39;999999999&#39;,NULL,NULL,NULL,&#39;PERSON&#39;,&#39;UNSATISFIED&#39;,&#39;ApplicationUpdate&#39;,&#39;Application_Update&#39;,&#39;20190222&#39;,&#39;20190223&#39;,&#39;20190224&#39;,&#39;01309757&#39;),
(&#39;19540309&#39;,&#39;Smith&#39;,&#39;Jon&#39;,&#39;100000166&#39;,&#39;3014925&#39;,&#39;3014925&#39;,&#39;999999999&#39;,&#39;20190220&#39;,&#39;Document added to wrong year requirement.&#39;,&#39;2020&#39;,&#39;AwardYear&#39;,&#39;SATISFIED&#39;,&#39;CertOne&#39;,&#39;Cert_one&#39;,&#39;20190221&#39;,&#39;20190220&#39;,&#39;20190220&#39;,&#39;01294555&#39;)
;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 &#39;@birthDate&#39;
,s.lastName as &#39;@lastName&#39;
,s.firstName as &#39;@firstName&#39;
,s.externalStudentId3 as &#39;@externalStudentId3&#39;
,s.externalStudentId2 as &#39;@externalStudentId2&#39;
,s.externalStudentId1 as &#39;@externalStudentId1&#39;
,s.socialSecurityNumber as &#39;@socialSecurityNumber&#39;,
(SELECT
d.documentScope as &#39;@documentScope&#39;
,d.reason as &#39;@reason&#39;
,d.status as &#39;@status&#39;
,d.documentName as &#39;@documentName&#39;
,d.externalDocumentId as &#39;@externalDocumentId&#39;
,d.dateReviewed as &#39;@dateReviewed&#39;
,d.dateReceived as &#39;@dateReceived&#39;
,d.dateCreated as &#39;@dateCreated&#39;
,d.documentRequirementExternalId as &#39;@documentRequirementExternalId&#39;
FROM dbo.[person] d
WHERE d.socialSecurityNumber=s.socialSecurityNumber
FOR XML PATH (&#39;documentData&#39;), TYPE)
FROM cte_person s
FOR XML PATH (&#39;student&#39;), ROOT (&#39;students&#39;);

Output:

&lt;students&gt;
&lt;student birthDate=&quot;19540309&quot; lastName=&quot;Smith&quot; firstName=&quot;Jon&quot; externalStudentId3=&quot;100000166&quot; externalStudentId2=&quot;3014925&quot; externalStudentId1=&quot;3014925&quot; socialSecurityNumber=&quot;999999999&quot;&gt;
&lt;documentData documentScope=&quot;PERSON&quot; status=&quot;UNSATISFIED&quot; documentName=&quot;ApplicationUpdate&quot; externalDocumentId=&quot;Application_Update&quot; dateReviewed=&quot;2019-02-22T00:00:00&quot; dateReceived=&quot;2019-02-23T00:00:00&quot; dateCreated=&quot;2019-02-24T00:00:00&quot; documentRequirementExternalId=&quot;01309757&quot; /&gt;
&lt;documentData documentScope=&quot;AwardYear&quot; reason=&quot;Document added to wrong year requirement.&quot; status=&quot;SATISFIED&quot; documentName=&quot;CertOne&quot; externalDocumentId=&quot;Cert_one&quot; dateReviewed=&quot;2019-02-21T00:00:00&quot; dateReceived=&quot;2019-02-20T00:00:00&quot; dateCreated=&quot;2019-02-20T00:00:00&quot; documentRequirementExternalId=&quot;01294555&quot; /&gt;
&lt;/student&gt;
&lt;/students&gt;

答案2

得分: 0

以下是翻译好的部分：

检查一下。我只使用了很少的列来展示这个概念。您可以轻松扩展它以适用于您的完整解决方案。

> SQL

    USE tempdb;
    GO
    
    -- DDL 和样本数据填充，开始
    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 和样本数据填充，结束
    
    ;WITH rs AS
    (
    	-- 仅选择与人员有关的列
    	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>

请注意，我已经省略了代码部分，只返回翻译好的SQL代码和输出。

英文:

Check it out. I used very few columns just to show the concept. You can easily extend it for your full solution.

> 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 (&#39;19540309&#39;,&#39;Smith&#39;,&#39;Jon&#39;,&#39;100000166&#39;,&#39;3014925&#39;,&#39;3014925&#39;,&#39;999999999&#39;,NULL,NULL,NULL,&#39;PERSON&#39;,&#39;UNSATISFIED&#39;,&#39;ApplicationUpdate&#39;,&#39;Application_Update&#39;,&#39;20190222&#39;,&#39;20190223&#39;,&#39;20190224&#39;,&#39;01309757&#39;)
, (&#39;19540309&#39;,&#39;Smith&#39;,&#39;Jon&#39;,&#39;100000166&#39;,&#39;3014925&#39;,&#39;3014925&#39;,&#39;999999999&#39;,&#39;20190220&#39;,&#39;Document added to wrong year requirement.&#39;,&#39;2020&#39;,&#39;AwardYear&#39;,&#39;SATISFIED&#39;,&#39;CertOne&#39;,&#39;Cert_one&#39;,&#39;20190221&#39;,&#39;20190220&#39;,&#39;20190220&#39;,&#39;01294555&#39;);
-- 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(&#39;documentData&#39;), TYPE)
FROM rs AS p
FOR XML PATH(&#39;person&#39;), ROOT(&#39;persons&#39;);

> Output

&lt;persons&gt;
&lt;person id=&quot;100000166&quot; firstName=&quot;Jon&quot; lastName=&quot;Smith&quot;&gt;
&lt;documentData id=&quot;100000166&quot; documentScope=&quot;PERSON&quot; documentName=&quot;ApplicationUpdate&quot; /&gt;
&lt;documentData id=&quot;100000166&quot; documentScope=&quot;AwardYear&quot; documentName=&quot;CertOne&quot; /&gt;
&lt;/person&gt;
&lt;/persons&gt;

huangapple
  • 本文由 发表于 2020年1月3日 22:31:18
  • 转载请务必保留本文链接：https://go.coder-hub.com/59580341.html
  • sql
  • sql-server
  • xml
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定