表数据到XMl [英] Table data to XMl
问题描述
在下面的输出中，我不想显示'< ProofRecipient CorrectionsRequired =" YES" />'
< code>
< ProofingDetails>
< JournalID> BCPT< / JournalID>
< ArticleID> 12030< / ArticleID>
< ReviewerDetails>
< ProofRecipient CorrectionsRequired =" YES">
< author>
<名称> - - < /名称>
<电子邮件> - < /电子邮件>
< / author>
< / ProofRecipient>
< ProofRecipient CorrectionsRequired =" YES">
<编辑>
<名称>生产编辑< /名称>
<电子邮件> bcpt@wiley.com< /电子邮件>
< / Editor>
< / ProofRecipient>
< ProofRecipient CorrectionsRequired =" YES" />
$
< ProofRecipient CorrectionsRequired =" YES" />
< / ReviewerDetails>
< VendorDetails>
<名称> sps< /名称>
< VendorEditorName> AMUL S< / VendorEditorName>
< VendorEditorEmail> amul@sps.co.in< / VendorEditorEmail>
$
< AdditionalEmail>< / AdditionalEmail>
< / VendorDetails>
< PEDetails>
< PEName> Marjorie Anne Sarmiento< / PEName>
< PEEmail> masarmient@wiley.com< / PEEmail>
< / PEDetails>
< / ProofingDetails>
以下是我的查询
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;选择
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; j.jour_scode AS JournalID，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; a.art_no AS ArticleID，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;（SELECT
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;（选择"是"&NBSP; AS [@CorrectionsRequired]，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;（SELECT
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ; art_auth_fname +""+ art_auth_sname&NBSP;如姓名，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; art_auth_mailid如电子邮件
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FROM WB_PMS..tblArticle a
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; WHERE&NBSP; jour_no = j.jour_no和art_no = @ ArtNo
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（'author'），TYPE）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（'ProofRecipient'），TYPE），''
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; ，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; / *（选择 '是' &NBSP; AS [@CorrectionsRequired]，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;（SELECT
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; mem_name&NBSP;&NBSP ;姓名，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; mem_mailid如电子邮件
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; FROM opsjnoroute一个
&nb属;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; WHERE&NBSP; j_no&NBSP; COLLATE DATABASE_DEFAULT = j.jour_no&NBSP;&NBSP;
COLLATE DATABASE_DEFAULT
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（'编辑'），TYPE）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; FOR XML PATH（'ProofRecipient'），TYPE）* /
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;（选择 '是' &NBSP; AS [@CorrectionsRequired]，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;（SELECT
&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP; COALESCE（mem_name， ''）&NBSP;&NBSP;如姓名，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; COALESCE（mem_mailid， ''）AS电子邮件
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; FROM opsjnorou a
&nb sp; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; WHERE&NBSP; j_no&NBSP; COLLATE DATABASE_DEFAULT = j.jour_no&NBSP;&NBSP;
COLLATE DATABASE_DEFAULT和mem_type ='U2'
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（'编辑'），TYPE）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（'ProofRecipient'），TYPE），
&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;（选择 '是' &NBSP; AS [@CorrectionsRequired]，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;（SELECT
&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP; COALESCE（mem_name， ''）&NBSP;&NBSP;如姓名，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; COALESCE（mem_mailid， ''）AS电子邮件
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; FROM opsjnorou a
&nb sp; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; WHERE&NBSP; j_no&NBSP; COLLATE DATABASE_DEFAULT = j.jour_no&NBSP;&NBSP;
COLLATE DATABASE_DEFAULT和mem_type ='U3'
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（'编辑'），TYPE）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（'ProofRecipient'），TYPE），$
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;（选择 '是' &NBSP; AS [@CorrectionsRequired]，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;（SELECT
&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP; COALESCE（mem_name， ''）&NBSP;&NBSP;如姓名，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; COALESCE（mem_mailid， ''）AS电子邮件
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; FROM opsjnorou a
&nb sp; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; WHERE&NBSP; j_no&NBSP; COLLATE DATABASE_DEFAULT = j.jour_no&NBSP;&NBSP;
COLLATE DATABASE_DEFAULT和mem_type ='U4'
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（'编辑'），TYPE）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（'ProofRecipient'），TYPE）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（''），TYPE） AS ReviewerDetails，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;（SELECT名称，VendorEditorName，VendorEditorEmail，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;（CASE WHEN&NBSP; RIGHT（AdditionalEmail，1）= ';' THEN LEFT（AdditionalEmail，LEN（AdditionalEmail）-1）&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;当&NBSP; LEFT（AdditionalEmail，1）= ';' THEN RIGHT（AdditionalEmail，LEN（AdditionalEmail）-1）
&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; ELSE AdditionalEmail END）&NBSP;&NBSP; AS AdditionalEmail
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; FROM
&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ; （SELECT
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; 'sps'AS名称，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; sps.user_name AS VendorEditorName，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; sps.user_email AS VendorEditorEmail，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; ISNULL（（例如，当eProofOPS_PE_Cc = 1并且tbljour1.jour_pe_mailid不为空时，那么tbljour1.jour_pe_mailid +';'
ELSE''END），''）+
;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; ISNULL（（CASE WHEN LEFT（editorcc.epusr_email，1）= ';' THEN RIGHT（editorcc.epusr_email，LEN（editorcc.epusr_email）-1）+ ';' ELSE
editorcc.epusr_email + ';' END） ，''）+
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; - ISNULL（（CASE WHEN eProofOPS_JourEdr_Cc = 1 AND&NBSP;&NBSP; editorcc.Epusr_email IS NOT NULL&NBSP; THEN&NBSP;&NBSP; editorcc.Epusr_email
+ ';' &NBSP; ELSE '' &NBSP; END）， ''）+
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; ISNULL（（CASE WHEN LEFT（ProofReadercc.epusr_email，1）= ';' THEN RIGHT（ProofReadercc.epusr_email，LEN（ProofReadercc.epusr_email）-1）+ ';' &NBSP;
ELSE ProofReadercc.epusr_email + ';' END），''）+
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; - ISNULL（（CASE WHEN eProofOPS_ProofReader_Cc = 1&NBSP; AND ProofReadercc.Epusr_email IS NOT NULL&NBSP;&NBSP; THEN&NBSP; ProofReadercc.Epusr_email
+ ';' ELSE '' &NBSP; END）， ''）+
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; ISNULL（eProofOPS_Others_Cc不为空时的情况 那么eProofOPS_Others_Cc ELSE''END，''） AS
AdditionalEmaiL
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; / *'sps'AS Name，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; sps.user_name AS VendorEditorName，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; sps.user_email AS VendorEditorEmail，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; （
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;（CASE&NBSP; WHEN（（CASE WHEN eProofOPS_PE_Cc = 1 THEN ISNULL（tbljour1.jour_pe_mailid， ''）+ ';' &NBSP; ELSE '' END））= ';' THEN
'' END）&NBSP;&NBSP +
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; （CASE WHEN（（例如eProofOPS_JourEdr_Cc = 1 那么ISNULL（editorcc.Epusr_email，''）+';' ELSE
'' END））=';'那么'' END）&NBSP; +
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;（CASE&NBSP; WHEN（（CASE WHEN eProofOPS_ProofReader_Cc = 1&NBSP;&NBSP;&NBSP;&NBSP; THEN&NBSP; ISNULL（ProofReadercc.Epusr_email， ''）
+ ';' ELSE '' &NBSP; END ））=';'那么'结束）+
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; （CASE WHEN（eProofOPS_Others_Cc不为空时的情况 那么ISNULL（eProofOPS_Others_Cc，''）
ELSE''END）=';'THEN''END）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP; ）AS AdditionalEmail * /
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FROM
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; [WB_PMS]。[dbo] .tblJournal tbljour1 LEFT OUTER JOIN [WB_PMS]。[dbo] .tblUser sps ON tbljour1.jour_editor_id COLLATE DATABASE_DEFAULT = sps.user_employeecode
COLLATE DATABASE_DEFAULT
;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; LEFT OUTER JOIN [WB_PMS]。[dbo] .tblUser tmp ON tbljour1.TypMgtPE COLLATE DATABASE_DEFAULT = tmp.user_employeecode COLLATE DATABASE_DEFAULT
和tmp.user_type IN（'8.7'，'8.10'，'8.14'）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; LEFT OUTER JOIN
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;（SELECT（CASE WHEN RIGHT（epusr_email，1）= ';' THEN
LEFT（epusr_email，LEN（epusr_email）-1）&NBSP; ELSE epusr_email END）作为epusr_email，epj_no从&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;（选择&NBSP;&NBSP;东西（（选择流延（ ';' 为为nvarchar（MAX））
+（CASE WHEN j1.eProofOPS_JourEdr_Cc = 1 AND epusr_email不是那么ISNULL（epusr_email，''）ELSE''END）来自 WB_PMS..tbljournal j1 INNER JOIN epjoutit ej1 ON j1.jour_no COLLATE DATABASE_DEFAULT = ej1.epj_no COLLATE DATABASE_DEFAULT
INNER JOIN&NBSP; epjobs JB1 ON ej1.epj_no = jb1.epj_no&NBSP;&NBSP; INNER JOIN epmember M1&NBSP; ON m1.epusr_code = jb1.epusr_code WH ERE jb1.Epj_no = ej.Epj_no AND m1.epuser_cat_id ='U3'
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; 用于xml路径（''） ），1,1，''）作为epusr_email，ej.epj_no
来自epjoutit ej
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; ）editorcc1）AS editorcc
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; ON editorcc.epj_no collate database_default = tbljour1.jour_no
collate database_default
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; LEFT OUTER JOIN
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;（SELECT（CASE WHEN RIGHT（epusr_email，1）= ';' THEN
LEFT（epusr_email，LEN（epusr_email）-1）&NBSP; ELSE epusr_email END）作为epusr_email，epj_no从&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;（选择&NBSP;&NBSP;东西（（选择流延（ ';' 为为nvarchar（MAX））
+（CASE WHEN j1.eProofOPS_ProofReader_Cc = 1 AND epusr_email IS NOT NULL 那么ISNULL（epusr_email，''）ELSE''END）来自 WB_PMS..tbljournal j1 INNER JOIN epjoutit ej1 ON j1.jour_no COLLATE DATABASE_DEFAULT = ej1.epj_no COLLATE
DATABASE_DEFAULT INNER JOIN&NBSP; epjobs JB1 ON ej1.epj_no = jb1.epj_no&NBSP;&NBSP; INNER JOIN epmember M1&NBSP; ON m1.epusr_code = j的b1.epusr_code WHERE jb1.Epj_no = ej.Epj_no AND m1.epuser_cat_id ='U4'
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; 用于xml路径（''） ），1,1，''）作为epusr_email，ej.epj_no
来自epjoutit ej
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; ）ProofReadercc1）AS ProofReadercc
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; ON ProofReadercc.epj_no collate database_default = tbljour1.jour_no
collate database_default
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; WHERE
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; tbljour1.jour_scode=@Jcode)T
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FOR XML PATH（''），TYPE）AS VendorDetails ，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;（SELECT
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; tbljour。 jour_pe_fname +""+ jour_pe_sname作为PEName，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP; tbljour.jour_pe_mailid AS PEEmail
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; FROM
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ; [WB_PMS] .. tblJournal tbljour WITH（NOLOCK）LEFT OUTER JOIN [WB_PMS] .. tblUser sps WITH（NOLOCK）ON tbljour.jour_editor_id COLLATE DATABASE_DEFAULT = sps。 user_employeecode
COLLATE DATABASE_DEFAULT
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ; LEFT OUTER JOIN [WB_PMS] .. tblUse r tmp WITH（NOLOCK）ON tbljour.TypMgtPE COLLATE DATABASE_DEFAULT = tmp.user_employeecode COLLATE DATABASE_DEFAULT
和tmp.user_type IN（'8.7'，'8.10'，'8.14'）
&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; WHERE
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; tbljour.jour_no = j.jour_no FOR XML PATH（''），TYPE）AS PEDetails
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; FROM WB_PMS..tbljournal j INNER JOIN WB_PMS..tblArticle a ON j.jour_no = a.jour_no
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; WHERE j.jour_scode=@Jcode和a.art_no=@ArtNo
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; FOR XML PATH（ ''），TYPE，元件，ROOT（ 'ProofingDetails'）
< /代码>
以下是我想要的输出
< ProofingDetails>
< JournalID> BCPT< / JournalID>
< ArticleID> 12030< / ArticleID>
< ReviewerDetails>
< ProofRecipient CorrectionsRequired =" YES">
< author>
<名称> - - < /名称>
<电子邮件> - < /电子邮件>
< / author>
< / ProofRecipient>
< ProofRecipient CorrectionsRequired =" YES">
<编辑>
<名称>生产编辑< /名称>
<电子邮件> bcpt@wiley.com< /电子邮件>
< / Editor>
< / ProofRecipient>
< / ReviewerDetails>
< VendorDetails>
<名称> sps< /名称>
< VendorEditorName> AMUL S< / VendorEditorName>
< VendorEditorEmail> amul@sps.co.in< / VendorEditorEmail>
$
< AdditionalEmail>< / AdditionalEmail>
< / VendorDetails>
< PEDetails>
< PEName> Marjorie Anne Sarmiento< / PEName>
< PEEmail> masarmient@wiley.com< / PEEmail>
< / PEDetails>
< / ProofingDetails>
Hi Raghunadhan，
在原始脚本中，当mem_type ='U4'和mem_type =时'U3'，该值可能为null。所以我删除了这两部分。请尝试以下脚本。
SELECT
j.jour_scode AS JournalID，
a.art_no AS ArticleID，
（SELECT
（SELECT'YES 'AS [@CorrectionsRequired]，
（SELECT
art_auth_fname +'"+ art_auth_sname AS名称，
art_auth_mailid AS电子邮件
FROM WB_PMS..tblArticle一个
其中jour_no = j.jour_no和art_no = @货号
FOR XML PATH（ '作家'），TYPE）
FOR XML PATH（ 'ProofRecipient'），TYPE） ''
，
/ *（SELECT'YES'AS [@CorrectionsRequired]，
（SELECT
mem_name AS名称，
mem_mailid AS电子邮件
来自opsjnoroute a
WHERE j_no COLLATE DATABASE_DEFAULT = j.jour_no COLLATE DATABASE_DEFAULT
FOR XML PATH（'Editor'），TYPE）
对于XML路径（ 'ProofRecipient'），TYPE）* /
（选择 '是' AS [@CorrectionsRequired]，
（SELECT
COALESCE（mem_name， ''）AS名称，
COALESCE（mem_mailid， ''）AS电子邮件
FROM opsjnoroute一个
，其中j_no COLLATE DATABASE_DEFAULT = j.jour_no COLLATE DATABASE_DEFAULT和mem_type = 'U2'
FOR XML PATH（ '编辑'），TYPE）
FOR XML PATH（ 'ProofRecipient'），TYPE）
FOR XML PATH（ ''），TYPE）AS ReviewerDetails，
（SELECT姓名，VendorEditorName，VendorEditorEmail，
（CASE WHEN RIGHT（AdditionalEmail，1）= ';' THEN LEFT（AdditionalEmail，LEN（AdditionalEmail）-1）
当LEFT（AdditionalEmail，1）= ';'THEN RIGHT（AdditionalEmail，LEN（AdditionalEmail）-1）
ELSE AdditionalEmail END）AS AdditionalEmail
FROM
（SELECT
'sps "姓名，
sps.user_name AS VendorEditorName，
sps.user_email AS VendorEditorEmail，
ISNULL（（CASE WHEN eProofOPS_PE_Cc = 1 AND tbljour1.jour_pe_mailid IS NOT NULL THEN tbljour1 .jour_pe_mailid + ';' ELSE '' END）， ''）+
ISNULL（（CASE WHEN LEFT（editorcc.epusr_email，1）= ';' THEN RIGHT（editorcc.epusr_email，LEN（editorcc.epusr_email） -1）+';'ELSE editorcc.epusr_email +';'END），''）+
- ISNULL（（例如，当eProofOPS_JourEdr_Cc = 1并且editorcc.Epusr_email不为空时，编辑器.Epusr_email +';' ELSE''END），''）+
ISNULL（左下的情况（ProofReadercc.epusr_email，1）=';'那么正确（ProofReadercc.epusr_email，LEN（ProofReadercc.epusr_email）-1）+'; 'ELSE ProofReadercc.epusr_email +';'END），''）+
- ISNULL（（例如，当eProofOPS_ProofReader_Cc = 1且ProofReadercc.Epusr_email不为空时，ProofReadercc.Epusr_email +';'ELSE''END）， ''）+
ISNULL（例如，当eProofOPS_Others_Cc不为空时，eProofOPS_Others_Cc ELSE''END，' ）AS AdditionalEmaiL
/ * 'SPS' 名称，
sps.user_name AS VendorEditorName，
sps.user_email AS VendorEditorEmail，
（
（CASE WHEN（（例如，当eProofOPS_PE_Cc = 1那么ISNULL（tbljour1.jour_pe_mailid，''）+';'ELSE''END））=';'THEN''END）+
（情况时（（例如eProofOPS_JourEdr_Cc） = 1那么ISNULL（editorcc.Epusr_email，''）+';'ELSE''END））=';'那么'结束）+
（情况时（（例如eProofOPS_ProofReader_Cc = 1那么ISNULL（ProofReadercc） .Epusr_email，''）+';'ELSE''END））=';'那么'结束）+
（情况下的情况（例如，当eProofOPS_Others_Cc不为空时，则为ISNULL（eProofOPS_Others_Cc，''）ELSE' 'END）=';'那么'E ND）
）作为AdditionalEmail * /
从
[WB_PMS] [DBO] .tblJournal tbljour1 LEFT OUTER JOIN [WB_PMS]。[ dbo] .tblUser sps ON tbljour1.jour_editor_id COLLATE DATABASE_DEFAULT = sps.user_employeecode COLLATE DATABASE_DEFAULT
LEFT OUTER JOIN [WB_PMS]。[dbo] .tblUser tmp ON tbljour1.TypMgtPE COLLATE DATABASE_DEFAULT = tmp.user_employeecode COLLATE DATABASE_DEFAULT和tmp.user_type IN（ '8.7'， '8.10'， '8.14'）
LEFT OUTER JOIN
（SELECT（CASE WHEN RIGHT（epusr_email，1）= ';' THEN LEFT（epusr_email，LEN（epusr_email） - 1）ELSE epusr_email END）作为epusr_email，epj_no来自
（选择东西（（选择强制转换（';'为nvar） char（max））+（例如，当j1.eProofOPS_JourEdr_Cc = 1并且epusr_email不为空，那么ISNULL（epusr_email，''）ELSE''END）来自WB_PMS..tbljournal j1 INNER JOIN epjoutit ej1 ON j1.jour_no COLLATE DATABASE_DEFAULT = ej1 .epj_no COLLATE DATABASE_DEFAULT INNER JOIN epjobs jb1 ON ej1.epj_no = jb1.epj_no INNER JOIN epmember m1 ON m1.epusr_code = jb1.epusr_code WHERE jb1.Epj_no = ej.Epj_no AND m1.epuser_cat_id ='U3'
for xml路径（ ''）），1,1， ''）作为epusr_email，从ej.epj_no epjoutit EJ
）中editorcc1）AS editorcc
关于editorcc.epj_no分页database_default = tbljour1.jour_no整理database_default
LEFT OUTER JOIN
（SELECT（右边的情况（epusr_email，1）=';'然后左（epusr_email，LEN（epusr_email）-1）ELSE epusr_email END）作为epusr_email，epj_无论从
（选择的东西（（SELECT CAST（ ';' 作为为nvarchar（MAX））+（CASE WHEN j1.eProofOPS_ProofReader_Cc = 1 AND epusr_email IS NOT NULL THEN ISNULL（epusr_email， ''）ELSE '' END）来自WB_PMS..tbljournal j1 INNER JOIN epjoutit ej1 ON j1.jour_no COLLATE DATABASE_DEFAULT = ej1.epj_no COLLATE DATABASE_DEFAULT INNER JOIN epjobs jb1 ON ej1.epj_no = jb1.epj_no INNER JOIN epmember m1 ON m1.epusr_code = jb1.epusr_code WHERE jb1.Epj_no = ej.Epj_no AND m1.epuser_cat_id ='U4'
for xml path（''）），1,1，''）作为epusr_email，ej.epj_no来自epjoutit ej
）ProofReadercc1）AS ProofReadercc
ON ProofReadercc.epj_no collate database_default = tbljour1.jour_no collate database_default
WHERE
tbljour1.jour_scode = @Jcode）T
。对于XML PATH（ ''），TYPE）AS VendorDetails，
（SELECT
tbljour.jour_pe_fname +""+ jour_pe_sname作为PEName，
tbljour.jour_pe_mailid AS PEEmail
FROM
[WB_PMS] .. tblJournal tbljour WITH（NOLOCK）LEFT OUTER JOIN [WB_PMS] .. tblUser sps WITH（NOLOCK）ON tbljour.jour_editor_id COLLATE DATABASE_DEFAULT = sps.user_employeecode COLLATE DATABASE_DEFAULT
LEFT OUTER JOIN [WB_PMS] .. tblUser tmp WITH（NOLOCK）ON tbljour.TypMgtPE COLLATE DATABASE_DEFAULT = tmp.user_employeecode COLLATE DATABASE_DEFAULT和tmp.user_type IN（'8.7'，'8.10'，'8.14'）
WHERE
tbljour.jour_no = j.jour_no FOR XML PATH（''），TYPE）AS PEDetails
FROM WB_PMS..tbljournal j INNER JOIN WB_PMS..tblArticle a ON j .jour_no = a.jour_n o
WHERE j.jour_scode=@Jcode和a.art_no=@ArtNo
FOR XML PATH（''），TYPE，ELEMENTS，ROOT（'ProofingDetails'）
希望它可以帮到你。
最好的问候，
拉结
Hi,
In the below output i dont want to display '<ProofRecipient CorrectionsRequired="YES" />'
<code>
<ProofingDetails>
<JournalID>BCPT</JournalID>
<ArticleID>12030</ArticleID>
<ReviewerDetails>
<ProofRecipient CorrectionsRequired="YES">
<author>
<Name>- -</Name>
<Email>-</Email>
</author>
</ProofRecipient>
<ProofRecipient CorrectionsRequired="YES">
<Editor>
<Name>Production Editor</Name>
<Email>bcpt@wiley.com</Email>
</Editor>
</ProofRecipient>
<ProofRecipient CorrectionsRequired="YES" />
<ProofRecipient CorrectionsRequired="YES" />
</ReviewerDetails>
<VendorDetails>
<Name>sps</Name>
<VendorEditorName>AMUL S</VendorEditorName>
<VendorEditorEmail> amul@sps.co.in</VendorEditorEmail>
<AdditionalEmail></AdditionalEmail>
</VendorDetails>
<PEDetails>
<PEName>Marjorie Anne Sarmiento</PEName>
<PEEmail>masarmient@wiley.com</PEEmail>
</PEDetails>
</ProofingDetails>
Below is my query
SELECT
j.jour_scode AS JournalID,
a.art_no AS ArticleID,
(SELECT
(SELECT 'YES' AS [@CorrectionsRequired] ,
(SELECT
art_auth_fname +' '+art_auth_sname AS Name,
art_auth_mailid AS Email
FROM WB_PMS..tblArticle a
WHERE jour_no =j.jour_no and art_no=@ArtNo
FOR XML PATH ('author'), TYPE )
FOR XML PATH ('ProofRecipient'), TYPE),''
,
/*(SELECT 'YES' AS [@CorrectionsRequired] ,
(SELECT
mem_name AS Name,
mem_mailid AS Email
FROM opsjnoroute a
WHERE j_no COLLATE DATABASE_DEFAULT =j.jour_no
COLLATE DATABASE_DEFAULT
FOR XML PATH ('Editor'), TYPE )
FOR XML PATH ('ProofRecipient'), TYPE) */
(SELECT 'YES' AS [@CorrectionsRequired] ,
(SELECT
COALESCE(mem_name,'') AS Name,
COALESCE(mem_mailid,'') AS Email
FROM opsjnoroute a
WHERE j_no COLLATE DATABASE_DEFAULT =j.jour_no
COLLATE DATABASE_DEFAULT AND mem_type='U2'
FOR XML PATH ('Editor'), TYPE )
FOR XML PATH ('ProofRecipient'), TYPE),
(SELECT 'YES' AS [@CorrectionsRequired] ,
(SELECT
COALESCE(mem_name,'') AS Name,
COALESCE(mem_mailid,'') AS Email
FROM opsjnoroute a
WHERE j_no COLLATE DATABASE_DEFAULT =j.jour_no
COLLATE DATABASE_DEFAULT AND mem_type='U3'
FOR XML PATH ('Editor'), TYPE )
FOR XML PATH ('ProofRecipient'), TYPE),
(SELECT 'YES' AS [@CorrectionsRequired] ,
(SELECT
COALESCE(mem_name,'') AS Name,
COALESCE(mem_mailid,'') AS Email
FROM opsjnoroute a
WHERE j_no COLLATE DATABASE_DEFAULT =j.jour_no
COLLATE DATABASE_DEFAULT AND mem_type='U4'
FOR XML PATH ('Editor'), TYPE )
FOR XML PATH ('ProofRecipient'), TYPE)
FOR XML PATH (''), TYPE ) AS ReviewerDetails,
(SELECT Name,VendorEditorName,VendorEditorEmail,
(CASE WHEN RIGHT(AdditionalEmail,1)=';' THEN LEFT(AdditionalEmail,LEN(AdditionalEmail)-1)
WHEN LEFT(AdditionalEmail,1)=';' THEN RIGHT(AdditionalEmail,LEN(AdditionalEmail)-1)
ELSE AdditionalEmail END ) AS AdditionalEmail
FROM
(SELECT
'sps' AS Name,
sps.user_name AS VendorEditorName,
sps.user_email AS VendorEditorEmail,
ISNULL((CASE WHEN eProofOPS_PE_Cc=1 AND tbljour1.jour_pe_mailid IS NOT NULL THEN tbljour1.jour_pe_mailid +';'
ELSE '' END),'')+
ISNULL((CASE WHEN LEFT(editorcc.epusr_email,1)=';' THEN RIGHT(editorcc.epusr_email,LEN(editorcc.epusr_email)-1) +';' ELSE
editorcc.epusr_email+';' END),'')+
-- ISNULL((CASE WHEN eProofOPS_JourEdr_Cc=1 AND editorcc.Epusr_email IS NOT NULL THEN editorcc.Epusr_email
+ ';' ELSE '' END),'') +
ISNULL((CASE WHEN LEFT(ProofReadercc.epusr_email,1)=';' THEN RIGHT(ProofReadercc.epusr_email,LEN(ProofReadercc.epusr_email)-1)+';'
ELSE ProofReadercc.epusr_email+';' END),'')+
-- ISNULL((CASE WHEN eProofOPS_ProofReader_Cc= 1 AND ProofReadercc.Epusr_email IS NOT NULL THEN ProofReadercc.Epusr_email
+';' ELSE '' END),'')+
ISNULL( CASE WHEN eProofOPS_Others_Cc IS NOT NULL THEN eProofOPS_Others_Cc ELSE '' END,'') AS
AdditionalEmaiL
/*'sps' AS Name,
sps.user_name AS VendorEditorName,
sps.user_email AS VendorEditorEmail,
(
(CASE WHEN ((CASE WHEN eProofOPS_PE_Cc=1 THEN ISNULL(tbljour1.jour_pe_mailid,'') +';' ELSE '' END))=';' THEN
'' END) +
(CASE WHEN ((CASE WHEN eProofOPS_JourEdr_Cc=1 THEN ISNULL(editorcc.Epusr_email,'') +';' ELSE
'' END))=';' THEN '' END) +
(CASE WHEN ((CASE WHEN eProofOPS_ProofReader_Cc= 1 THEN ISNULL(ProofReadercc.Epusr_email,'')
+';' ELSE '' END))=';' THEN '' END)+
(CASE WHEN ( CASE WHEN eProofOPS_Others_Cc IS NOT NULL THEN ISNULL(eProofOPS_Others_Cc,'')
ELSE '' END)=';' THEN '' END)
) AS AdditionalEmail*/
FROM
[WB_PMS].[dbo].tblJournal tbljour1 LEFT OUTER JOIN [WB_PMS].[dbo].tblUser sps ON tbljour1.jour_editor_id COLLATE DATABASE_DEFAULT=sps.user_employeecode
COLLATE DATABASE_DEFAULT
LEFT OUTER JOIN [WB_PMS].[dbo].tblUser tmp ON tbljour1.TypMgtPE COLLATE DATABASE_DEFAULT = tmp.user_employeecode COLLATE DATABASE_DEFAULT
and tmp.user_type IN ('8.7','8.10','8.14')
LEFT OUTER JOIN
(SELECT (CASE WHEN RIGHT(epusr_email,1)=';' THEN
LEFT(epusr_email,LEN(epusr_email)-1) ELSE epusr_email END) as epusr_email,epj_no from
(Select stuff((select cast(';' as nvarchar(max))
+ (CASE WHEN j1.eProofOPS_JourEdr_Cc=1 AND epusr_email IS NOT NULL THEN ISNULL(epusr_email,'') ELSE '' END) from WB_PMS..tbljournal j1 INNER JOIN epjoutit ej1 ON j1.jour_no COLLATE DATABASE_DEFAULT=ej1.epj_no COLLATE DATABASE_DEFAULT
INNER JOIN epjobs jb1 ON ej1.epj_no=jb1.epj_no INNER JOIN epmember m1 ON m1.epusr_code=jb1.epusr_code WHERE jb1.Epj_no= ej.Epj_no AND m1.epuser_cat_id='U3'
for xml path('') ), 1, 1, '') as epusr_email,ej.epj_no
from epjoutit ej
) editorcc1) AS editorcc
ON editorcc.epj_no collate database_default =tbljour1.jour_no
collate database_default
LEFT OUTER JOIN
(SELECT (CASE WHEN RIGHT(epusr_email,1)=';' THEN
LEFT(epusr_email,LEN(epusr_email)-1) ELSE epusr_email END) as epusr_email,epj_no from
(Select stuff((select cast(';' as nvarchar(max))
+ (CASE WHEN j1.eProofOPS_ProofReader_Cc=1 AND epusr_email IS NOT NULL THEN ISNULL(epusr_email,'') ELSE '' END) from WB_PMS..tbljournal j1 INNER JOIN epjoutit ej1 ON j1.jour_no COLLATE DATABASE_DEFAULT=ej1.epj_no COLLATE
DATABASE_DEFAULT INNER JOIN epjobs jb1 ON ej1.epj_no=jb1.epj_no INNER JOIN epmember m1 ON m1.epusr_code=jb1.epusr_code WHERE jb1.Epj_no= ej.Epj_no AND m1.epuser_cat_id='U4'
for xml path('') ), 1, 1, '') as epusr_email,ej.epj_no
from epjoutit ej
) ProofReadercc1) AS ProofReadercc
ON ProofReadercc.epj_no collate database_default=tbljour1.jour_no
collate database_default
WHERE
tbljour1.jour_scode=@Jcode)T
FOR XML PATH (''), TYPE ) AS VendorDetails ,
(SELECT
tbljour.jour_pe_fname +' '+jour_pe_sname As PEName,
tbljour.jour_pe_mailid AS PEEmail
FROM
[WB_PMS]..tblJournal tbljour WITH(NOLOCK) LEFT OUTER JOIN [WB_PMS]..tblUser sps WITH(NOLOCK) ON tbljour.jour_editor_id COLLATE DATABASE_DEFAULT=sps.user_employeecode
COLLATE DATABASE_DEFAULT
LEFT OUTER JOIN [WB_PMS]..tblUser tmp WITH(NOLOCK) ON tbljour.TypMgtPE COLLATE DATABASE_DEFAULT = tmp.user_employeecode COLLATE DATABASE_DEFAULT
and tmp.user_type IN ('8.7','8.10','8.14')
WHERE
tbljour.jour_no=j.jour_no FOR XML PATH (''), TYPE) AS PEDetails
FROM WB_PMS..tbljournal j INNER JOIN WB_PMS..tblArticle a ON j.jour_no=a.jour_no
WHERE j.jour_scode=@Jcode and a.art_no=@ArtNo
FOR XML PATH('') , TYPE,ELEMENTS,ROOT ('ProofingDetails')
</code>
Below is the output i want
<ProofingDetails>
<JournalID>BCPT</JournalID>
<ArticleID>12030</ArticleID>
<ReviewerDetails>
<ProofRecipient CorrectionsRequired="YES">
<author>
<Name>- -</Name>
<Email>-</Email>
</author>
</ProofRecipient>
<ProofRecipient CorrectionsRequired="YES">
<Editor>
<Name>Production Editor</Name>
<Email>bcpt@wiley.com</Email>
</Editor>
</ProofRecipient>
</ReviewerDetails>
<VendorDetails>
<Name>sps</Name>
<VendorEditorName>AMUL S</VendorEditorName>
<VendorEditorEmail> amul@sps.co.in</VendorEditorEmail>
<AdditionalEmail></AdditionalEmail>
</VendorDetails>
<PEDetails>
<PEName>Marjorie Anne Sarmiento</PEName>
<PEEmail>masarmient@wiley.com</PEEmail>
</PEDetails>
</ProofingDetails>
Hi Raghunadhan,
In your original script , when mem_type='U4' and mem_type='U3', the value might be null. So I delete these two parts. Please try following script.
SELECT j.jour_scode AS JournalID, a.art_no AS ArticleID, (SELECT (SELECT 'YES' AS [@CorrectionsRequired] , (SELECT art_auth_fname +' '+art_auth_sname AS Name, art_auth_mailid AS Email FROM WB_PMS..tblArticle a WHERE jour_no =j.jour_no and art_no=@ArtNo FOR XML PATH ('author'), TYPE ) FOR XML PATH ('ProofRecipient'), TYPE),'' , /*(SELECT 'YES' AS [@CorrectionsRequired] , (SELECT mem_name AS Name, mem_mailid AS Email FROM opsjnoroute a WHERE j_no COLLATE DATABASE_DEFAULT =j.jour_no COLLATE DATABASE_DEFAULT FOR XML PATH ('Editor'), TYPE ) FOR XML PATH ('ProofRecipient'), TYPE) */ (SELECT 'YES' AS [@CorrectionsRequired] , (SELECT COALESCE(mem_name,'') AS Name, COALESCE(mem_mailid,'') AS Email FROM opsjnoroute a WHERE j_no COLLATE DATABASE_DEFAULT =j.jour_no COLLATE DATABASE_DEFAULT AND mem_type='U2' FOR XML PATH ('Editor'), TYPE ) FOR XML PATH ('ProofRecipient'), TYPE) FOR XML PATH (''), TYPE ) AS ReviewerDetails, (SELECT Name,VendorEditorName,VendorEditorEmail, (CASE WHEN RIGHT(AdditionalEmail,1)=';' THEN LEFT(AdditionalEmail,LEN(AdditionalEmail)-1) WHEN LEFT(AdditionalEmail,1)=';' THEN RIGHT(AdditionalEmail,LEN(AdditionalEmail)-1) ELSE AdditionalEmail END ) AS AdditionalEmail FROM (SELECT 'sps' AS Name, sps.user_name AS VendorEditorName, sps.user_email AS VendorEditorEmail, ISNULL((CASE WHEN eProofOPS_PE_Cc=1 AND tbljour1.jour_pe_mailid IS NOT NULL THEN tbljour1.jour_pe_mailid +';' ELSE '' END),'')+ ISNULL((CASE WHEN LEFT(editorcc.epusr_email,1)=';' THEN RIGHT(editorcc.epusr_email,LEN(editorcc.epusr_email)-1) +';' ELSE editorcc.epusr_email+';' END),'')+ -- ISNULL((CASE WHEN eProofOPS_JourEdr_Cc=1 AND editorcc.Epusr_email IS NOT NULL THEN editorcc.Epusr_email + ';' ELSE '' END),'') + ISNULL((CASE WHEN LEFT(ProofReadercc.epusr_email,1)=';' THEN RIGHT(ProofReadercc.epusr_email,LEN(ProofReadercc.epusr_email)-1)+';' ELSE ProofReadercc.epusr_email+';' END),'')+ -- ISNULL((CASE WHEN eProofOPS_ProofReader_Cc= 1 AND ProofReadercc.Epusr_email IS NOT NULL THEN ProofReadercc.Epusr_email +';' ELSE '' END),'')+ ISNULL( CASE WHEN eProofOPS_Others_Cc IS NOT NULL THEN eProofOPS_Others_Cc ELSE '' END,'') AS AdditionalEmaiL /*'sps' AS Name, sps.user_name AS VendorEditorName, sps.user_email AS VendorEditorEmail, ( (CASE WHEN ((CASE WHEN eProofOPS_PE_Cc=1 THEN ISNULL(tbljour1.jour_pe_mailid,'') +';' ELSE '' END))=';' THEN '' END) + (CASE WHEN ((CASE WHEN eProofOPS_JourEdr_Cc=1 THEN ISNULL(editorcc.Epusr_email,'') +';' ELSE '' END))=';' THEN '' END) + (CASE WHEN ((CASE WHEN eProofOPS_ProofReader_Cc= 1 THEN ISNULL(ProofReadercc.Epusr_email,'') +';' ELSE '' END))=';' THEN '' END)+ (CASE WHEN ( CASE WHEN eProofOPS_Others_Cc IS NOT NULL THEN ISNULL(eProofOPS_Others_Cc,'') ELSE '' END)=';' THEN '' END) ) AS AdditionalEmail*/ FROM [WB_PMS].[dbo].tblJournal tbljour1 LEFT OUTER JOIN [WB_PMS].[dbo].tblUser sps ON tbljour1.jour_editor_id COLLATE DATABASE_DEFAULT=sps.user_employeecode COLLATE DATABASE_DEFAULT LEFT OUTER JOIN [WB_PMS].[dbo].tblUser tmp ON tbljour1.TypMgtPE COLLATE DATABASE_DEFAULT = tmp.user_employeecode COLLATE DATABASE_DEFAULT and tmp.user_type IN ('8.7','8.10','8.14') LEFT OUTER JOIN (SELECT (CASE WHEN RIGHT(epusr_email,1)=';' THEN LEFT(epusr_email,LEN(epusr_email)-1) ELSE epusr_email END) as epusr_email,epj_no from (Select stuff((select cast(';' as nvarchar(max)) + (CASE WHEN j1.eProofOPS_JourEdr_Cc=1 AND epusr_email IS NOT NULL THEN ISNULL(epusr_email,'') ELSE '' END) from WB_PMS..tbljournal j1 INNER JOIN epjoutit ej1 ON j1.jour_no COLLATE DATABASE_DEFAULT=ej1.epj_no COLLATE DATABASE_DEFAULT INNER JOIN epjobs jb1 ON ej1.epj_no=jb1.epj_no INNER JOIN epmember m1 ON m1.epusr_code=jb1.epusr_code WHERE jb1.Epj_no= ej.Epj_no AND m1.epuser_cat_id='U3' for xml path('') ), 1, 1, '') as epusr_email,ej.epj_no from epjoutit ej ) editorcc1) AS editorcc ON editorcc.epj_no collate database_default =tbljour1.jour_no collate database_default LEFT OUTER JOIN (SELECT (CASE WHEN RIGHT(epusr_email,1)=';' THEN LEFT(epusr_email,LEN(epusr_email)-1) ELSE epusr_email END) as epusr_email,epj_no from (Select stuff((select cast(';' as nvarchar(max)) + (CASE WHEN j1.eProofOPS_ProofReader_Cc=1 AND epusr_email IS NOT NULL THEN ISNULL(epusr_email,'') ELSE '' END) from WB_PMS..tbljournal j1 INNER JOIN epjoutit ej1 ON j1.jour_no COLLATE DATABASE_DEFAULT=ej1.epj_no COLLATE DATABASE_DEFAULT INNER JOIN epjobs jb1 ON ej1.epj_no=jb1.epj_no INNER JOIN epmember m1 ON m1.epusr_code=jb1.epusr_code WHERE jb1.Epj_no= ej.Epj_no AND m1.epuser_cat_id='U4' for xml path('') ), 1, 1, '') as epusr_email,ej.epj_no from epjoutit ej ) ProofReadercc1) AS ProofReadercc ON ProofReadercc.epj_no collate database_default=tbljour1.jour_no collate database_default WHERE tbljour1.jour_scode=@Jcode)T FOR XML PATH (''), TYPE ) AS VendorDetails , (SELECT tbljour.jour_pe_fname +' '+jour_pe_sname As PEName, tbljour.jour_pe_mailid AS PEEmail FROM [WB_PMS]..tblJournal tbljour WITH(NOLOCK) LEFT OUTER JOIN [WB_PMS]..tblUser sps WITH(NOLOCK) ON tbljour.jour_editor_id COLLATE DATABASE_DEFAULT=sps.user_employeecode COLLATE DATABASE_DEFAULT LEFT OUTER JOIN [WB_PMS]..tblUser tmp WITH(NOLOCK) ON tbljour.TypMgtPE COLLATE DATABASE_DEFAULT = tmp.user_employeecode COLLATE DATABASE_DEFAULT and tmp.user_type IN ('8.7','8.10','8.14') WHERE tbljour.jour_no=j.jour_no FOR XML PATH (''), TYPE) AS PEDetails FROM WB_PMS..tbljournal j INNER JOIN WB_PMS..tblArticle a ON j.jour_no=a.jour_no WHERE j.jour_scode=@Jcode and a.art_no=@ArtNo FOR XML PATH('') , TYPE,ELEMENTS,ROOT ('ProofingDetails')
Hope it can help you.
Best Regards,
Rachel
这篇关于表数据到XMl的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
