Empty closing XML tag instead of self-closing tagOracle GoldenGate add trandata errorsSHOWPLAN does not...
How do I avoid the "chosen hero" feeling?
What does @ mean in a hostname in DNS configuration?
Aligning Systems of Equations
Spells that would be effective against a Modern Day army but would NOT destroy a fantasy one
Why is Bernie Sanders maximum accepted donation on actblue $5600?
When distributing a Linux kernel driver as source code, what's the difference between Proprietary and GPL license?
How does holding onto an active but un-used credit card affect your ability to get a loan?
How can I portray body horror and still be sensitive to people with disabilities?
Can a planet be tidally unlocked?
For the Circle of Spores druid's Halo of Spores feature, is your reaction used regardless of whether the other creature succeeds on the saving throw?
How can I make my enemies feel real and make combat more engaging?
Variance of sine and cosine of a random variable
How can guns be countered by melee combat without raw-ability or exceptional explanations?
How does the spell Slow affect freefall?
Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once
Is it common to refer to someone as "Prof. Dr. [LastName]"?
What does "don't have a baby" imply or mean in this sentence?
Cryptic cross... with words
Did the characters in Moving Pictures not know about cameras like Twoflower's?
SQL Server 2017 crashes when backing up because filepath is wrong
Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?
Why write a book when there's a movie in my head?
What is the name of this perspective and how is it constructed?
Sets which are both Sum-free and Product-free.
Empty closing XML tag instead of self-closing tag
Oracle GoldenGate add trandata errorsSHOWPLAN does not display a warning but “Include Execution Plan” does for the same queryAdding 2 Case statementsSingle value from a list of comma separated values when used in inner join in SQL ServerCan I make this multiple join query faster?Performance gap between WHERE IN (1,2,3,4) vs IN (select * from STRING_SPLIT('1,2,3,4',','))Replacing isnull condition in where clauseInvestigating errors from strange querychanging from inner joins to left joins to include null values?Measure Agent Job failure and running jobs with 'execution_status'
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')
sql-server xml
asked Feb 15 at 5:51
RaghuRaghu
63
|
show 5 more comments
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')
sql-server xml
asked Feb 15 at 5:51
RaghuRaghu
63
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
|
show 5 more comments
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')
sql-server xml
asked Feb 15 at 5:51
RaghuRaghu
63
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')
sql-server xml
sql-server xml
asked Feb 15 at 5:51
RaghuRaghu
63
asked Feb 15 at 5:51
RaghuRaghu
63
edited 15 mins ago
Raghu
asked Feb 15 at 5:51
RaghuRaghu
63
asked Feb 15 at 5:51
RaghuRaghu
63
asked Feb 15 at 5:51
RaghuRaghu
63
63
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
|
show 5 more comments
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
2
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
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
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
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
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
Do you want the output to be "<AdditionalEmail/>" or "<AdditionalEmail></AdditionalEmail>" when there is no Additional Email value?
– HandyD
Feb 15 at 7:01
|
show 5 more comments
2 Answers
2
active
oldest
votes
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.
answered Feb 15 at 8:38
GlorfindelGlorfindel
9771816
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
add a comment |
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.
answered Feb 17 at 22:06
HandyDHandyD
949112
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
21 hours ago
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
6 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229817%2fempty-closing-xml-tag-instead-of-self-closing-tag%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Feb 15 at 8:38
GlorfindelGlorfindel
9771816
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
add a comment |
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.
answered Feb 15 at 8:38
GlorfindelGlorfindel
9771816
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
add a comment |
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.
answered Feb 15 at 8:38
GlorfindelGlorfindel
9771816
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.
answered Feb 15 at 8:38
GlorfindelGlorfindel
9771816
edited Feb 15 at 8:46
answered Feb 15 at 8:38
GlorfindelGlorfindel
9771816
answered Feb 15 at 8:38
GlorfindelGlorfindel
9771816
answered Feb 15 at 8:38
GlorfindelGlorfindel
9771816
9771816
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
add a comment |
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
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
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
Hi all help me out
– Raghu
Feb 15 at 9:32
add a comment |
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.
answered Feb 17 at 22:06
HandyDHandyD
949112
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
21 hours ago
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
6 hours ago
add a comment |
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.
answered Feb 17 at 22:06
HandyDHandyD
949112
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
21 hours ago
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
6 hours ago
add a comment |
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.
answered Feb 17 at 22:06
HandyDHandyD
949112
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.
answered Feb 17 at 22:06
HandyDHandyD
949112
edited Feb 19 at 2:26
answered Feb 17 at 22:06
HandyDHandyD
949112
answered Feb 17 at 22:06
HandyDHandyD
949112
answered Feb 17 at 22:06
HandyDHandyD
949112
949112
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
21 hours ago
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
6 hours ago
add a comment |
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
21 hours ago
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
6 hours ago
Hi HandyD, I tried as you said its not working
– Raghu
Feb 18 at 4:30
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
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
21 hours ago
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
21 hours ago
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
6 hours ago
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
6 hours ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229817%2fempty-closing-xml-tag-instead-of-self-closing-tag%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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