SQL stored procedure using FOR XML PATH having issues getting the right nesting of the XML layout

Question

I have tried many different iterations of this, using other answered questions on here and I am just not getting this right. This is my code:

CREATE PROCEDURE [dbo].[ImportHCCAssessment]
AS

DECLARE
@SecurityUserId uniqueidentifier = 'EBE4BD0F-DD05-4A6E-BFE8-BF5CAC0CF4C5',
@SecurityUserContextId uniqueidentifier = '42CACC5F-3DA7-45E5-A656-15A3570A444C',
@AssessmentTemplateId uniqueidentifier = 
    (
    SELECT AssessmentTemplateId 
    FROM MedCompass.dbo.AssessmentTemplate 
    WHERE TemplateName = 'Hierarchical Condition Category'
    AND ActiveFlag = 1
    )

---HCC Assessment Generate XML for Insert

;WITH HCC AS (
SELECT *
FROM MemberHCC
UNPIVOT
(HCCValue FOR HCCCode IN 
(       HCC001, HCC002, HCC006, HCC008, HCC009, HCC010, HCC011, HCC017, HCC018, HCC019, HCC021, HCC022, HCC023, HCC027, HCC028,
        HCC029, HCC033, HCC034, HCC035, HCC039, HCC040, HCC046, HCC047, HCC048, HCC051, HCC052, HCC054, HCC055, HCC056, HCC057,
        HCC058, HCC059, HCC060, HCC070, HCC071, HCC072, HCC073, HCC074, HCC075, HCC076, HCC077, HCC078, HCC079, HCC080, HCC082,
        HCC083, HCC084, HCC085, HCC086, HCC087, HCC088, HCC096, HCC099, HCC100, HCC103, HCC104, HCC106, HCC107, HCC108, HCC110,
        HCC111, HCC112, HCC114, HCC115, HCC122, HCC124, HCC134, HCC135, HCC136, HCC137, HCC138, HCC157, HCC158, HCC159, HCC161,
        HCC162, HCC166, HCC167, HCC169, HCC170, HCC173, HCC176, HCC186, HCC188, HCC189)
) AS unpvt
WHERE RecordTypeCode = 'J'
)
SELECT (
    SELECT GeneratedGuid AS "@id"
        , 'Question' AS "@language"
        , 'true' AS "@hasPrepopulated"
        , '0' AS "Score/@Column"
        , 'true' AS "Score/@IncInRollup"
        , '' AS "Score/@Method"
        , '' AS "Score/@ScoreName"
        , '0' AS "Score/@Value"
        , ap.AssessmentPageId AS "Page/@id"
        , 'Visible' AS "Page/@InitialState"
        , 'true' AS "Page/@IsEnabled"
        , 'false' AS "Page/@IsRequired"
        , '0' AS "Page/Score/@Column"
        , 'true' AS "Page/Score/@IncInRollup"
        , '' AS "Page/Score/@Method"
        , '' AS "Page/Score/@ScoreName"
        , '0' AS "Page/Score/@Value"
        , atb.AssessmentTabId AS "Page/Tab/@id"
        , 'Visible' AS "Page/Tab/@InitialState"
        , 'true' AS "Page/Tab/@IsEnabled"
        , 'false' AS "Page/Tab/@IsRequired"
        , '0' AS "Page/Tab/Score/@Column"
        , 'true' AS "Page/Tab/Score/@IncInRollup"
        , '' AS "Page/Tab/Score/@Method"
        , '' AS "Page/Tab/Score/@ScoreName"
        , '0' AS "Page/Tab/Score/@Value"
        , asec.AssessmentSectionId AS "Page/Tab/Section/@id"
        , 'Visible' AS "Page/Tab/Section/@InitialState"
        , 'true' AS "Page/Tab/Section/@IsEnabled"
        , 'false' AS "Page/Tab/Section/@IsRequired"
        , '1' AS "Page/Tab/Section/@Sequence"
        , 'false' AS "Page/Tab/Section/@Repeatable"
        , '1' AS "Page/Tab/Section/@RepeatedSequence"
        , '1' AS "Page/Tab/Section/@ActiveFlag"
        , '1' AS "Page/Tab/Section/@EditFlag"
        , '0' AS "Page/Tab/Section/Score/@Column"
        , 'true' AS "Page/Tab/Section/Score/@IncInRollup"
        , '' AS "Page/Tab/Section/Score/@Method"
        , '' AS "Page/Tab/Section/Score/@ScoreName"
        , '0' AS "Page/Tab/Section/Score/@Value"
        , aq.AssessmentQuestionId AS "Page/Tab/Section/Question/@id"
        , 'Visible' AS "Page/Tab/Section/Question/@InitialState"
        , 'true' AS "Page/Tab/Section/Question/@IsEnabled"
        , 'false' AS "Page/Tab/Section/Question/@IsRequired"
        , 'a3e1965c-d050-4709-a554-644564722c54' AS "Page/Tab/Section/Question/@Code"
        , 'false' AS "Page/Tab/Section/Question/@DisplayCode"
        , '0' AS "Page/Tab/Section/Question/Score/@Column"
        , 'true' AS "Page/Tab/Section/Question/Score/@IncInRollup"
        , '' AS "Page/Tab/Section/Question/Score/@Method"
        , '' AS "Page/Tab/Section/Question/Score/@ScoreName"
        , '0' AS "Page/Tab/Section/Question/Score/@Value"
        , aa.AssessmentAnswerId AS "Page/Tab/Section/Question/Answer/@id"
        , 'CheckBox' AS "Page/Tab/Section/Question/Answer/@Type"
        , 'Visible' AS "Page/Tab/Section/Question/Answer/@InitialState"
        , 'true' AS "Page/Tab/Section/Question/Answer/@IsEnabled"
        , 'false' AS "Page/Tab/Section/Question/Answer/@IsRequired"
        , 'false' AS "Page/Tab/Section/Question/Answer/@copy"
        , '1' AS "Page/Tab/Section/Question/Answer/@Sequence"
        , 'false' AS "Page/Tab/Section/Question/Answer/@IncInSummary"
        , '' AS "Page/Tab/Section/Question/Answer/@SummaryTitle"
        , 'false' AS "Page/Tab/Section/Question/Answer/@AlphaSorting"
        , 'b94ec9a9-77eb-41da-b62d-9f7d70b2f2d4' AS "Page/Tab/Section/Question/Answer/@Code"
        , 'false' AS "Page/Tab/Section/Question/Answer/@DisplayCode"
        , 'false' AS "Page/Tab/Section/Question/Answer/@DisplayAnswerName"
        , '' AS "Page/Tab/Section/Question/Answer/@Value"
        , '1' AS "Page/Tab/Section/Question/Answer/@QuestionSequence"
        , ( 
            SELECT ali.AssessmentListItemId AS "Page/Tab/Section/Question/Answer/ListItem/@ListItemId"
            , '0.00' AS "Page/Tab/Section/Question/Answer/ListItem/@Score1"
            , '0.00' AS "Page/Tab/Section/Question/Answer/ListItem/@Score2"
            , '0.00' AS "Page/Tab/Section/Question/Answer/ListItem/@Score3"
            , '' AS "Page/Tab/Section/Question/Answer/ListItem/@Score1ProgramTypeKey"
            , '' AS "Page/Tab/Section/Question/Answer/ListItem/@Score2ProgramTypeKey"       
            , '' AS  "Page/Tab/Section/Question/Answer/ListItem/@Score3ProgramTypeKey"
            , HCCValue AS  "Page/Tab/Section/Question/Answer/ListItem/@Selected"
            , '0' AS "Page/Tab/Section/Question/Answer/ListItem/@Sequence"
            , '' AS "Page/Tab/Section/Question/Answer/ListItem/@FullDescription"
            FROM  HCC hcc
                JOIN MedCompass.dbo.AssessmentListItem ali
                ON LEFT(ali.ListItem, 6) = hcc.HCCCode
                FOR XML PATH ('ListItem'), TYPE
            ) 
        FOR XML PATH ('Assmt')
        ) XMLfile   
        , hcc.MBI
    , GeneratedGuid
INTO #XMLTempHCC
FROM MemberHCC hcc
JOIN MedCompass.dbo.AssessmentTemplate atp
    ON 1 = 1
    AND atp.AssessmentTemplateId = @AssessmentTemplateId
JOIN MedCompass.dbo.AssessmentPage ap
    ON ap.AssessmentTemplateId = atp.AssessmentTemplateId
    AND atp.AssessmentTemplateId = @AssessmentTemplateId
JOIN MedCompass.dbo.AssessmentTab atb
    ON atb.AssessmentPageId = ap.AssessmentPageId
JOIN MedCompass.dbo.AssessmentSection asec
    ON asec.AssessmentTabId = atb.AssessmentTabId
JOIN MedCompass.dbo.AssessmentQuestion aq
    ON aq.AssessmentSectionId = asec.AssessmentSectionId
JOIN MedCompass.dbo.AssessmentAnswer aa
    ON aa.AssessmentQuestionId = aq.AssessmentQuestionId
JOIN MedCompass.dbo.AssessmentList al
    ON aa.AssessmentListId = al.AssessmentListId
LEFT JOIN (
    SELECT NEWID() AS GeneratedGuid
    ) AS GG
    ON 1 = 1

This is the XML format I'm trying to get:

This is what my above code is producing:

I'm obviously "nesting" things wrong, I just can seem to figure out how to fix it. All of the ListItem elements are supposed to be a one-to-many relationship of records UNDER the Answer element. (Note: XML Samples are cut down to stay in the posting character limit, but there's enough to get the idea)

SQL stored procedure using FOR XML PATH having issues getting the right nesting of the XML layout

0 Answers