2

My query is

USE ...

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

                    /*'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  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(replace(cast(@XML as nvarchar(max)), '<ProofRecipient CorrectionsRequired="YES"/>', ''), '<AdditionalEmail></AdditionalEmail>','<AdditionalEmail/>')
        -- 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

In the below Output in the <AdditionalEmail> tag there is no content display as <AdditionalEmail/> I want the output as empty tag <AdditionalEmail></AdditionalEmail>.

<VendorDetails>

  <Name>sps</Name>
    <VendorEditorName>KASTHURI DINESH</VendorEditorName>
    <VendorEditorEmail>kasthuri.dinesh@sps.co.in</VendorEditorEmail>
    <AdditionalEmail/> *this shoud be* <AdditionalEmail><AdditionalEmail/>
</VendorDetails>

Hi HandyD,

Please find my updated query i have used path instead of raw but correctionsrequired tag appears . I dont want to display correctionsrequired tag.

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') 

  • 2
    Please provide more information. A sample data set, the query you're executing and the expected results. Also include details of the version of SQL Server and what you've tried so far. – HandyD Feb 15 at 6:00
  • Help me iam new to this – Raghu Feb 15 at 6:20
  • giving like this <VendorDetails> <Name xmlns:xsi="w3.org/2001/XMLSchema-instance">sps</Name> <VendorEditorName xmlns:xsi="w3.org/2001/XMLSchema-instance">AMUL S</VendorEditorName> <VendorEditorEmail xmlns:xsi="w3.org/2001/XMLSchema-instance"> amul@sps.co.in</VendorEditorEmail> <AdditionalEmail xmlns:xsi="w3.org/2001/XMLSchema-instance" /> – Raghu Feb 15 at 6:53
  • I want as <VendorDetails> <Name >sps</Name> <VendorEditorName >AMULS</VendorEditorName> <VendorEditorEmail> amul@sps.co.in</VendorEditorEmail> </AdditionalEmail></AdditionalEmail> </VendorDetails> – Raghu Feb 15 at 6:55
  • Do you want the output to be "<AdditionalEmail/>" or "<AdditionalEmail></AdditionalEmail>" when there is no Additional Email value? – HandyD Feb 15 at 7:01
0

Your current query contains a line

SET @XML = replace(replace(cast(@XML as nvarchar(max)),
                           '<ProofRecipient CorrectionsRequired="YES"/>', ''),
                   '<AdditionalEmail></AdditionalEmail>','<AdditionalEmail/>')

(it's one long line, about the 10th one counting from below).

I've introduced line breaks to make it more visible what is going on; it looks like you're deliberately replacing the empty tag. The second argument of the REPLACE function ('<AdditionalEmail></AdditionalEmail>') is what it should look for, and the third argument ('<AdditionalEmail/>') is what it will be replaced with.

  • No.sorry wrongly codded .I need output as <AdditionalEmail></AdditionalEmail> when additional email is empty. Even when i write like this SET @XML = replace(replace(cast(@XML as nvarchar(max)), '<ProofRecipient CorrectionsRequired="YES"/>', ''), '<AdditionalEmail/>', '<AdditionalEmail></AdditionalEmail>').Iam getting the same output – Raghu Feb 15 at 8:44
  • Hi all help me out – Raghu Feb 15 at 9:32
0

There are a couple of answers on SO that should point you in the right direction:

Here, and Here

To summarise, you should try using FOR XML PATH instead of FOR XML RAW to try and avoid the self-closing tag, however, as Martin Smith has pointed out in the comments to your question, semantically self-closing and empty tags are the same, so they should work in either format unless the tool ingesting the XML is reading it as a string instead of an XML document.

Below is an example of this in action:

CREATE TABLE XmlTestData (ID INT IDENTITY, PossiblyEmptyColumn VARCHAR(255))
GO

INSERT INTO XmlTestData (PossiblyEmptyColumn)
VALUES ('Test 1'), (NULL), ('Test 3')
GO

SELECT ID,
    COALESCE(PossiblyEmptyColumn, '') AS PossiblyEmptyColumn
FROM XmlTestData
FOR XML PATH('Row'), TYPE, ELEMENTS, ROOT('Data')

This produces the below output:

<Data>
  <Row>
    <ID>1</ID>
    <PossiblyEmptyColumn>Test 1</PossiblyEmptyColumn>
  </Row>
  <Row>
    <ID>2</ID>
    <PossiblyEmptyColumn></PossiblyEmptyColumn>
  </Row>
  <Row>
    <ID>3</ID>
    <PossiblyEmptyColumn>Test 3</PossiblyEmptyColumn>
  </Row>
</Data>

In the code you've provided, you're explicitly replacing the empty tags with self-closing tags using this line:

SET  @XML = replace(replace(cast(@XML as nvarchar(max)), '<ProofRecipient CorrectionsRequired="YES"/>', ''), '<AdditionalEmail></AdditionalEmail>','<AdditionalEmail/>')

Replace that with this:

SET  @XML = replace(cast(@XML as nvarchar(max)), '<ProofRecipient CorrectionsRequired="YES"/>', '')

And your empty tags will not be replaced with self-closing tags, provided you've switched to using XML FOR PATH instead of XML FOR RAW.

  • Hi HandyD, I tried as you said its not working – Raghu Feb 18 at 4:30
  • I've added some examples to clarify. The main issue seems to be switching between use of XML FOR PATH and XML FOR RAW, and then doing a REPLACE that switches to self-closing tags. – HandyD Feb 19 at 2:26
  • Thank you Handyd.Since iam assinging the output to XML datatype variable @xml its converting to self closing tag.As you said it returns empty tag if i return xml result as its is without assignment.The issue is again assigning to xml datatype variable.Then tell me how to replace '<ProofRecipient CorrectionsRequired="YES"/>' to empty – Raghu Feb 21 at 8:33
  • If its always going to be empty, and based on your script it is always "YES" so will always be empty, can you not simply remove it from the script? Note also that the reason "CorrectionsRequired" appears as a property of the tag is the use of RAW instead of PATH. – HandyD Feb 22 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.