我需要从我的SQL服务器视图中为四种检查类型生成以下XML结构。XML 结构如下所示。
==
<Form FormIdentifier="Major Approvals (EPIC:EPIC Test)" CompanyCode="EPIC:EPIC Test" CompanyName="EPIC Test" VesselCode="EPBARN" SubmittedDate="2019-05-22T08:00:00" Status="Submitted" ApprovedDate="" ImoNumber="9251121">
<VesselName>EPIC BARNES</VesselName>
<VoyageNo></VoyageNo>
<IMO_Number>9251121</IMO_Number>
<SIRE>
<SIRERow>
<Company__Terminal2>SHELL</Company__Terminal2>
<Last_Inspected>2019-05-22T12:00:00+00:00</Last_Inspected>
<No_of_Obs>1</No_of_Obs>
<Risk_Rating>2</Risk_Rating>
<ve_Screening_Yes_No>3</ve_Screening_Yes_No>
<Comments>4</Comments>
<Expiry_Date>2019-05-23T12:00:00+00:00</Expiry_Date>
<Planned_Date>2019-05-31T12:00:00+00:00</Planned_Date>
<Planned_Port>SINGAPORE</Planned_Port>
<Plannng_Comments>5</Plannng_Comments>
<Observations_closed_out2>6</Observations_closed_out2>
</SIRERow>
</SIRE>
<Non_SIRE>
<Non_SIRERow>
<Company__Terminal1>BP</Company__Terminal1>
<Last_Inspected1>2019-05-01T12:00:00+00:00</Last_Inspected1>
<No_of_Obs1>1</No_of_Obs1>
<Risk_Rating1>2</Risk_Rating1>
<ve_Screening_Yes_No1>3</ve_Screening_Yes_No1>
<Comments1>4</Comments1>
<Expiry_Date1>2019-05-22T12:00:00+00:00</Expiry_Date1>
<Planned_Date1>2019-05-31T12:00:00+00:00</Planned_Date1>
<Planned_Port1>KERTEH</Planned_Port1>
<Planning_Comments>5</Planning_Comments>
<Observations_closed_out1>6</Observations_closed_out1>
</Non_SIRERow>
</Non_SIRE>
<Additional_Screening>
<Additional_ScreeningRow>
<Company__Terminal>EXXON</Company__Terminal>
<Last_Inspected2>2019-05-01T12:00:00+00:00</Last_Inspected2>
<No_of_Obs2>1</No_of_Obs2>
<Risk_Rating2>2</Risk_Rating2>
<ve_Screening_Yes_No2>3</ve_Screening_Yes_No2>
<Comments2>4</Comments2>
<Expiry_Date2>2019-05-22T12:00:00+00:00</Expiry_Date2>
<Planned_Date_>2019-05-31T12:00:00+00:00</Planned_Date_>
<Planned_Port2>OSAKA</Planned_Port2>
<Planning_Comments1>5</Planning_Comments1>
<Observations_closed_out>6</Observations_closed_out>
</Additional_ScreeningRow>
</Additional_Screening>
</Form>
=====
我有以下查询，使用 XML 路径和 TYPE 来创建上面的 XML 文件。就语法而言，它工作正常，但从不返回确切的记录数。对于一个或两个记录，它工作正常，对于更多记录，它要么未完全生成，要么显示较少的记录
DECLARE @vsl AS varchar(50) = 'Sea Fortune 1';
DECLARE @imo AS varchar(50) = '9293741';
SELECT 'Major Approvals (EPIC:EPIC Test)' AS "@FormIdentifier",
'EPIC:EPIC Test' AS "@CompanyCode",
'EPIC Test' AS "@CompanyName",
'' AS "@VesselCode",
GETDATE() AS "@SubmittedDate",
'Submitted' AS "@Status",
'' AS "@ApprovedDate",
@imo AS "@ImoNumber",
@vsl AS VesselName,
'' AS VoyageNo,
@imo AS IMONO,
(SELECT otmajorname AS "SIRERow/Company__Terminal2",
inspectedOn AS "SIRERow/Last_Inspected",
tobs AS "SIRERow/No_of_Obs2",
riskrating AS "SIRERow/Risk_Rating2",
pscreen AS "SIRERow/ve_Screening_Yes_No2",
comment AS "SIRERow/Comments2",
ApprovalTo AS "SIRERow/Expiry_Date",
plplandate AS "SIRERow/Planned_Date",
plport AS "SIRERow/Planned_Port",
remark AS "SIRERow/Plannng_Comments",
openobs AS "SIRERow/Observations_closed_out2"
FROM RptXMLepic
WHERE vtIMONo = @imo
AND InspType = 'SIRE'
FOR XML PATH('SIRE'), TYPE),
(SELECT otmajorname AS "Non_SIRERow/Company__Terminal2",
inspectedOn AS "Non_SIRERow/Last_Inspected",
tobs AS "Non_SIRERow/No_of_Obs2",
riskrating AS "Non_SIRERow/Risk_Rating2",
pscreen AS "Non_SIRERow/ve_Screening_Yes_No2",
comment AS "Non_SIRERow/Comments2",
ApprovalTo AS "Non_SIRERow/Expiry_Date",
plplandate AS "Non_SIRERow/Planned_Date",
plport AS "Non_SIRERow/Planned_Port",
remark AS "Non_SIRERow/Plannng_Comments",
openobs AS "Non_SIRERow/Observations_closed_out2"
FROM RptXMLepic
WHERE vtIMONo = @imo
AND InspType = 'NON- SIRE'
FOR XML PATH('Non_SIRE'), TYPE),
(SELECT otmajorname AS "CDIRow/Company__Terminal2",
inspectedOn AS "CDIRow/Last_Inspected",
tobs AS "CDIRow/No_of_Obs2",
riskrating AS "CDIRow/Risk_Rating2",
pscreen AS "CDIRow/ve_Screening_Yes_No2",
comment AS "CDIRow/Comments2",
ApprovalTo AS "CDIRow/Expiry_Date",
plplandate AS "CDIRow/Planned_Date",
plport AS "CDIRow/Planned_Port",
remark AS "CDIRow/Plannng_Comments",
openobs AS "CDIRow/Observations_closed_out2"
FROM RptXMLepic
WHERE vtIMONo = @imo
AND InspType = 'CDI'
FOR XML PATH('CDI'), TYPE),
(SELECT otmajorname AS "Addional_ScreeningRow/Company__Terminal2",
inspectedOn AS "Addional_ScreeningRow/Last_Inspected",
tobs AS "Addional_ScreeningRow/No_of_Obs2",
riskrating AS "Addional_ScreeningRow/Risk_Rating2",
pscreen AS "Addional_ScreeningRow/ve_Screening_Yes_No2",
comment AS "Addional_ScreeningRow/Comments2",
ApprovalTo AS "Addional_ScreeningRow/Expiry_Date",
plplandate AS "Addional_ScreeningRow/Planned_Date",
plport AS "Addional_ScreeningRow/Planned_Port",
remark AS "Addional_ScreeningRow/Plannng_Comments",
openobs AS "Addional_ScreeningRow/Observations_closed_out2"
FROM RptXMLepic
WHERE vtIMONo = @imo
AND InspType = 'Screen'
FOR XML PATH('Addional_Screening'), TYPE)
FOR XML PATH('Form');
GO
我的数据位于视图 RptXMLepic。请你帮我解决问题的确切位置。
这只是我的错误. 在网上进行了大量搜索后，查询是完美的，我什么也没找到。 实际上我正在做的只是从查询窗口复制 XML 输出并粘贴到一些 noteapd++ 编辑器上。但是整个 XML 文本没有被复制。所以最后我点击了输出，这是等等！我的意思是，它只在SQL管理工作室中打开得很好，只有所有数据。对不起，伙计们，我现在很放松..谢谢