替换自闭标签 [英] replacing self closing tag

查看：81 发布时间：2019/6/15 3:40:07 transactsql

本文介绍了替换自闭标签的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何替换< 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; <电子邮件> swsleung@hku.hk< /电子邮件>

   &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> kasthuri.dinesh@sps.co.in< / VendorEditorEmail>

   &NBSP;&NBSP;&NBSP; &的< AdditionalEmail />这应该是
< AdditionalEmail> < AdditionalEmail />

   < / VendorDetails>

   < PEDetails>

   &NBSP;&NBSP;&NBSP; < PEName> Malathi Balamurugan< / PEName>

   &NBSP;&NBSP;&NBSP; < PEEmail> mbalamurug@wiley.com< / 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>swsleung@hku.hk</Email>
           </author>
       </ProofRecipient>
   </ReviewerDetails>
   <VendorDetails>
       <Name>sps</Name>
       <VendorEditorName>KASTHURI DINESH</VendorEditorName>
       <VendorEditorEmail>kasthuri.dinesh@sps.co.in</VendorEditorEmail>
       <AdditionalEmail/> this shoud be <AdditionalEmail><AdditionalEmail/>
   </VendorDetails>
   <PEDetails>
       <PEName>Malathi Balamurugan</PEName>
       <PEEmail>mbalamurug@wiley.com</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

替换自闭标签 [英] replacing self closing tag

问题描述

推荐答案

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

替换自闭标签 [英] replacing self closing tag

问题描述

推荐答案

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭