问题描述
如何替换< AdditionalEmail /> 在转换查询时自动关闭标记以清空标记 以下查询中的数据到xml
How to replace < AdditionalEmail/> self closing tag to empty tag while converting query data to xml in the below query
并查看下面的输出
<？xml version =" 1.0"？>
< ProofingDetails>
< JournalID> APHA< / JournalID>
< ArticleID> 12628< / ArticleID>
< ReviewerDetails>
&NBSP;&NBSP;&NBSP; < ProofRecipient CorrectionsRequired =" YES">
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; < author>
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; < Name> Susan WS Leung< / Name>
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; <电子邮件> [email protected]< /电子邮件>
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; < / author>
&NBSP;&NBSP;&NBSP; < / ProofRecipient>
< / ReviewerDetails>
< VendorDetails>
&NBSP;&NBSP;&NBSP; < Name> sps< / Name>
&NBSP;&NBSP;&NBSP; < VendorEditorName> KASTHURI DINESH< / VendorEditorName>
&NBSP;&NBSP;&NBSP; < VendorEditorEmail> [email protected]< / VendorEditorEmail>
&NBSP;&NBSP;&NBSP; &的< AdditionalEmail />这应该是
< AdditionalEmail> < AdditionalEmail />
< / VendorDetails>
< PEDetails>
&NBSP;&NBSP;&NBSP; < PEName> Malathi Balamurugan< / PEName>
&NBSP;&NBSP;&NBSP; < PEEmail> [email protected]< / PEEmail>
&NBSP;< / PEDetails>
将/ ProofingDetails>
<?xml version="1.0"?>
<ProofingDetails>
<JournalID>APHA</JournalID>
<ArticleID>12628</ArticleID>
<ReviewerDetails>
<ProofRecipient CorrectionsRequired="YES">
<author>
<Name>Susan WS Leung</Name>
<Email>[email protected]</Email>
</author>
</ProofRecipient>
</ReviewerDetails>
<VendorDetails>
<Name>sps</Name>
<VendorEditorName>KASTHURI DINESH</VendorEditorName>
<VendorEditorEmail>[email protected]</VendorEditorEmail>
<AdditionalEmail/> this shoud be<AdditionalEmail><AdditionalEmail/>
</VendorDetails>
<PEDetails>
<PEName>Malathi Balamurugan</PEName>
<PEEmail>[email protected]</PEEmail>
</PEDetails>
</ProofingDetails>
使用[OPSBW]
GO
/ ******对象： StoredProcedure [dbo]。[usp_OPS_HTML_Proofing] 脚本日期：15-02-2019 09:32:31 ****** /
$
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON¥
GO
- ============================== ===============
- 作者： &NBSP;&NBSP;&NBSP; < Raghunadhan>
- 创建日期：< 2019-02-13>
- 说明： < HTML防伪>
- =================================== ============
$
- EXEC usp_OPS_HTML_Proofing'APHA'，'12628'
$
ALTER PROCEDURE&NBSP; [DBO] [usp_OPS_HTML_Proofing]
（
@Jcode VARCHAR（50）NULL，
@货号&NBSP;&NBSP;&NBSP; NVARCHAR（50 ）NULL
）
AS
BEGIN
DECLARE @XML XML
DECLARE @JNo nvarchar（100）
SET NOCOUNT ON;
SELECT @ JNO = jour_no&NBSP;来自WB_PMS..tblJournal WHERE jour_scode = @ Jcode
IF EXISTS（SELECT 1 FROM WB_PMS..tblArticle WHERE jour_no = @ JNo AND art_no = @ ArtNo）
BEGIN
&NBSP;&NBSP;&NBSP; SET @ XML =
&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; j.jour_scode AS&NBSP; JournalID，
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ; a.art_no AS ArticleID，
（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; AS名称，
&NBSP;&NBSP;&NBSP; &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 AS电子邮件
&NBSP;&NBSP;&NBSP; &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 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; 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 ; FROM opsjnorou 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; 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; 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 ; FROM opsjnorou 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; 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; 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 ; FROM opsjnorou 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; 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; 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; ELSE AdditionalEmail END）&NBSP;&NBSP; AS AdditionalEmail
&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;&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; 'SPS' 姓名，
&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 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 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;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&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; epusr_email，J.Epj_no从epjoutitĴINNER JOIN epjobs
JB ON j.epj_no = jb.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; INNER JOIN epmember米&NBSP; ON m.epusr_code = jb.epusr_code
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&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;&NBSP;&NBSP; epuser_cat_id = 'U3'）AS editorcc
&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; epusr_email，J.Epj_no从epjoutitĴINNER JOIN epjobs
JB ON j.epj_no = jb.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; INNER JOIN epmember米&NBSP; ON m.epusr_code = jb.epusr_code
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&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;&NBSP;&NBSP; epuser_cat_id = 'U4'）AS ProofReadercc
&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; FOR XML RAW（''），ROOT（'ProofingDetails'），ELEMENTS
&NBSP;&NBSP;&NBSP; ）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; SET&NBSP; @XML =替换（铸造（@XML如为nvarchar（MAX）），"< ProofRecipient CorrectionsRequired = QUOT;是" />"，""）
&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; SET @ XML =取代（铸造（@XML如为nvarchar（MAX））， '< AdditionalEmail />'， '< AdditionalEmail>< / AdditionalEmail>'）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; SELECT XMLPROOFING =（SELECT&NBSP; CONVERT（VARCHAR（MAX）， '<？XML版本= QUOT; 1.0"？>'）+ CONVERT（VARCHAR（MAX），（SELECT&NBSP;&NBSP; @XML）））
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; SELECT XMLPROOFING = @ XML
结束
ELSE
BEGIN
&NBSP;&NBSP;&NBSP; SELECT XMLPROOFING = NULL
END
结束
$
$
USE [OPSBW]
GO
/****** Object: StoredProcedure [dbo].[usp_OPS_HTML_Proofing] Script Date: 15-02-2019 09:32:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Raghunadhan>
-- Create date: <2019-02-13>
-- Description: <HTML PROOFING>
-- =============================================
-- EXEC usp_OPS_HTML_Proofing 'APHA','12628'
ALTER PROCEDURE [dbo].[usp_OPS_HTML_Proofing]
(
@Jcode VARCHAR(50) NULL,
@ArtNo NVARCHAR(50) NULL
)
AS
BEGIN
DECLARE @XML XML
DECLARE @JNo nvarchar(100)
SET NOCOUNT ON;
SELECT @JNo=jour_no from WB_PMS..tblJournal WHERE jour_scode=@Jcode
IF EXISTS (SELECT 1 FROM WB_PMS..tblArticle WHERE jour_no=@JNo AND art_no=@ArtNo)
BEGIN
SET @XML=
(
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
mem_name AS Name,
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
mem_name AS Name,
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
mem_name AS Name,
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) 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 eProofOPS_JourEdr_Cc=1 AND editorcc.Epusr_email IS NOT NULL THEN editorcc.Epusr_email + ';' ELSE '' 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
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 epusr_email,J.Epj_no from epjoutit j INNER JOIN epjobs jb ON j.epj_no=jb.epj_no
INNER JOIN epmember m ON m.epusr_code=jb.epusr_code
WHERE epuser_cat_id='U3') AS editorcc
ON editorcc.epj_no collate database_default =tbljour1.jour_no collate database_default
LEFT OUTER JOIN
(Select epusr_email,J.Epj_no from epjoutit j INNER JOIN epjobs jb ON j.epj_no=jb.epj_no
INNER JOIN epmember m ON m.epusr_code=jb.epusr_code
WHERE epuser_cat_id='U4') 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 RAW('') , ROOT ('ProofingDetails'), ELEMENTS
)
SET @XML = replace(cast(@XML as nvarchar(max)), '<ProofRecipient CorrectionsRequired="YES"/>', '')
SET @XML=replace(cast(@XML as nvarchar(max)), '<AdditionalEmail/>', '<AdditionalEmail></AdditionalEmail>')
SELECT XMLPROOFING= (SELECT CONVERT(VARCHAR(MAX),'<?xml version="1.0"?>') +CONVERT(VARCHAR(MAX),( SELECT @XML)) )
SELECT XMLPROOFING=@XML
END
ELSE
BEGIN
SELECT XMLPROOFING=NULL
END
END
推荐答案
这篇关于替换自闭标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！