Empty closing XML tag instead of self-closing tagOracle GoldenGate add trandata errorsSHOWPLAN does not...
Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once
Isn't a semicolon (';') needed after a function declaration in C++?
Can you wish for more wishes from an Efreeti bound to service via an Efreeti Bottle?
How to play songs that contain one guitar when we have two or more guitarists?
In the Lost in Space intro why was Dr. Smith actor listed as a special guest star?
How can I make my enemies feel real and make combat more engaging?
Is it possible to detect 100% of SQLi with a simple regex?
SQL Server 2017 crashes when backing up because filepath is wrong
Why write a book when there's a movie in my head?
How Create a list of the first 10,000 digits of Pi and sum it?
How do I write a maintainable, fast, compile-time bit-mask in C++?
Are encryption algorithms with fixed-point free permutations inherently flawed?
Found a major flaw in paper from home university – to which I would like to return
What does an unprocessed RAW file look like?
How bad is a Computer Science course that doesn't teach Design Patterns?
What is the reason behind this musical reference to Pinocchio in the Close Encounters main theme?
How do I avoid the "chosen hero" feeling?
What's the function of the word "ли" in the following contexts?
Can I combine Divination spells with Arcane Eye?
Taking an academic pseudonym?
When distributing a Linux kernel driver as source code, what's the difference between Proprietary and GPL license?
Have the UK Conservatives lost the working majority and if so, what does this mean?
A cancellation property for permutations?
Now...where was I?
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 11 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