如何使用存储过程在 XML SQL 中使用不同的 [英] how to using distinct in XML SQL using stored procedure

问题描述

SELECT DISTINCT
    CD.CASE_NO AS CaseNumber , 
    CE.PROCEEDING_ID AS ProceedingId, 
    PR.AC_STATE_CD AS ForeclosureStatecode, 
    0 AS STATUS, 
    GETDATE() AS CreatedDate, 
    1 AS CreatedBy, 
    GETDATE() AS ModifiedDate, 
    1 AS ModifiedBy,
            (
            select * from TBL_PROPERTY FOR XML PATH('T')
            )
            from TBL_CASE_DETAIL CD WITH(NOLOCK)
                    INNER JOIN TBL_AUCTION_DETAIL AD WITH(NOLOCK) ON AD.CASE_DETAIL_ID = CD.CASE_DETAIL_ID
                    LEFT JOIN TBL_AUCTION_PROPERTY_MAPPING TAPM WITH(NOLOCK) ON TAPM.AUCTION_ID = AD.AUCTION_ID
                    LEFT JOIN TBL_PROPERTY PR WITH(NOLOCK) ON PR.PROPERTY_ID = TAPM.PROPERTY_ID
                    LEFT JOIN TBL_AUCTION_PROCEEDING_MAPPING APM WITH(NOLOCK) ON APM.AUCTION_ID = AD.AUCTION_ID

    FOR XML PATH('Foreclosure')

大家好，这是我修改后的代码以获得不同的结果.但现在属性部分无法正常显示..

Hi Guys, This is my modified code to get a distinct result. but now property part cannot display properly..

这是我得到的结果.假设打开另一个属性选项卡，假设有 3 个属性..所以在止赎的 1 个选项卡中，止赎内有 3 个属性数据，然后关闭止赎选项卡..
https://gyazo.com/8a54690c88df3e9bb0dd7ff916c6f86c谢谢你.

This is the result I get it. suppose open another tab for property lets say have 3 property.. so in 1 tab of foreclosure,inside foreclosure have 3 property data, then close foreclose tab..
https://gyazo.com/8a54690c88df3e9bb0dd7ff916c6f86c Thank You.

推荐答案

如果我做对了，您唯一需要添加的就是 ,TYPE.否则你的内部 SELECT ... FOR XML PATH() 被填充为文本，所有禁止字符都被转义...

If I get this right, the only thing you have to add is ,TYPE. Otherwise your inner SELECT ... FOR XML PATH() is filled i as text with all forbidden characters escaped...

试试:

(
    select * from TBL_PROPERTY FOR XML PATH('T'),TYPE
)

这篇关于如何使用存储过程在 XML SQL 中使用不同的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！