January 26, 2018 at 6:33 am
Hi, I'm sorry if this post is a little long, I am trying to include only relevant information.
Currently I have a script exporting the contents of a temporary table to XML, however one of the child nodes is causing the parent node to duplicate, I would like to combine them into a single node.
DECLARE @CRWGroups TABLE (ClientID INT, intRegistrationWizardID INT, nvhProfileItemName VARCHAR(MAX), intProfileItemID INT, bitSelected BIT, intProfileItemOptionItemID INT, bitAnswer BIT
,dteAnswerStart DATE, decWeight DEC(5,2))
INSERT INTO @CRWGroups (ClientID, intRegistrationWizardID, nvhProfileItemName, intProfileItemID, bitSelected, intProfileItemOptionItemID, bitAnswer, dteAnswerStart, decWeight)
select crw.intClientID, crw.intRegistrationWizardID, pi.nvhProfileItemName, crwegipi.intProfileItemID, crwegipi.bitSelected, pioi.intProfileItemOptionItemID,
crwegipib.bitAnswer, crwegipid.dteAnswerStart, pioi.decWeight from tblClientRegistrationWizard crw
INNER JOIN tblClientRegistrationWizardElement crwe ON crwe.intClientRegistrationWizardID = crw.intClientRegistrationWizardID
INNER JOIN tblClientRegistrationWizardElementGroup crweg ON crweg.intClientRegistrationWizardElementID = crwe.intClientRegistrationWizardElementID
LEFT JOIN tblClientRegistrationWizardElementGroupItem_ProfileItem crwegipi ON crwegipi.intClientRegistrationWizardElementGroupID = crweg.intClientRegistrationWizardElementGroupID
LEFT JOIN tblClientRegistrationWizardElementGroupItem_ProfileItemOptionItem crwegipioi ON crwegipioi.intClientRegistrationWizardElementGroupItemProfileItemID = crwegipi.intClientRegistrationWizardElementGroupItemProfileItemID
LEFT JOIN tblClientRegistrationWizardElementGroupItem_ProfileItemBool crwegipib ON crwegipib.intClientRegistrationWizardElementGroupItemProfileItemID = crwegipi.intClientRegistrationWizardElementGroupItemProfileItemID
LEFT JOIN tblClientRegistrationWizardElementGroupItem_ProfileItemDate crwegipid ON crwegipid.intClientRegistrationWizardElementGroupItemProfileItemID = crwegipi.intClientRegistrationWizardElementGroupItemProfileItemID
LEFT join tblProfileItem pi ON pi.intProfileItemID = crwegipi.intProfileItemID
LEFT JOIN tblProfileItemOptionItem pioi ON pioi.intProfileItemOptionItemID = crwegipioi.intProfileItemOptionItemID
LEFT JOIN tblProfileItemBool pib ON pib.intProfileItemID = crwegipib.intClientRegistrationWizardElementGroupItemProfileItemID
WHERE crw.intRegistrationWizardID = 49 AND (crwegipi.bitSelected = 1 OR crwegipib.bitAnswer = 1)
SELECT(
SELECT
(SELECT DISTINCT
CASE WHEN cg.intProfileItemID IN (5174,4864) THEN '10'
WHEN cg.intProfileItemID = 4862 THEN '11' END
FROM @CRWGroups cg WHERE cg.intProfileItemID IN (5174,4864,4862) AND cg.ClientID = c.ClientID
FOR XML PATH ('EmpStat'),type)
,(SELECT DISTINCT
CASE WHEN cg.intProfileItemID IN (4864,5174,4862) THEN (SELECT ISNULL(CASE WHEN CONVERT(DATE, cg.dteAnswerStart) = '1900-01-01' THEN '' ELSE CONVERT(CHAR(10), cg.dteAnswerStart, 120) END, '')) END
FROM @CRWGroups cg WHERE cg.intProfileItemID IN (4864,5174,4862) AND cg.ClientID = c.ClientID
FOR XML PATH ('DateEmpStatApp'),type)
,(SELECT CASE WHEN cg.intProfileItemID IN (5180) THEN (SELECT 'LOU' AS 'ESMType', '2' AS 'ESMCode' FOR XML PATH ('EmploymentStatusMonitoring'),type) END)
,(SELECT CASE WHEN cg.intProfileItemID IN (4868) THEN (SELECT 'BSI' AS 'ESMType', '1' AS 'ESMCode' FOR XML PATH ('EmploymentStatusMonitoring'),type) END)
FROM @CRWGroups cg WHERE cg.ClientID = c.ClientID AND cg.intProfileItemID IN (5180,4868) AND c.ClientID = 10740
GROUP BY cg.intProfileItemID FOR XML PATH ('LearnerEmploymentStatus'),type)
FROM tblClient c
INNER JOIN tblClientRegistrationWizard crw ON crw.intClientID = c.ClientID WHERE crw.intRegistrationWizardID = 49
An output of the table for a single client with only the relevant profile items:
| ClientID | RegID | ProfileName | ProfileID | bitSelect | OptionID | bitAnswer | dteAnswer | decWeight |
|---|---|---|---|---|---|---|---|---|
| 10740 | 49 | Unemployed | 4862 | 1 | NULL | NULL | 2017-06-05 | NULL |
| 10740 | 49 | Job Seekers Allowance | 4868 | 1 | NULL | NULL | NULL | NULL |
| 10740 | 49 | Unemployed for 6 - 11 months | 5180 | 1 | NULL | NULL | NULL | NULL |
The current XML output: Please note that there are two <LearnerEmploymentStatus> nodes.
<LearnerEmploymentStatus>
<EmpStat>11</EmpStat>
<DateEmpStatApp>2017-06-05</DateEmpStatApp>
<EmploymentStatusMonitoring>
<ESMType>BSI</ESMType>
<ESMCode>1</ESMCode>
</EmploymentStatusMonitoring>
</LearnerEmploymentStatus>
<LearnerEmploymentStatus>
<EmpStat>11</EmpStat>
<DateEmpStatApp>2017-06-05</DateEmpStatApp>
<EmploymentStatusMonitoring>
<ESMType>LOU</ESMType>
<ESMCode>2</ESMCode>
</EmploymentStatusMonitoring>
</LearnerEmploymentStatus>
The desired output: Please note there is a single <LearnerEmploymentStatus> with two <EmploymentStatusMonitoring> nodes contained within.
<LearnerEmploymentStatus>
<EmpStat>11</EmpStat>
<DateEmpStatApp>2017-06-05</DateEmpStatApp>
<EmploymentStatusMonitoring>
<ESMType>BSI</ESMType>
<ESMCode>1</ESMCode>
</EmploymentStatusMonitoring>
<EmploymentStatusMonitoring>
<ESMType>LOU</ESMType>
<ESMCode>2</ESMCode>
</EmploymentStatusMonitoring>
</LearnerEmploymentStatus>
Any and all help on the issue will be greatly appreciated.
January 26, 2018 at 7:35 am
We can't see any sample data, so it's going to be harder to help. Can you generate dummy data and give us table structures so we can run it and play with the results?
That aside, you've got this GROUP BY happening: GROUP BY cg.intProfileItemID FOR XML PATH ('LearnerEmploymentStatus'),type), which I assume is why you have two <learnerEmploymentStatus> nodes. Without seeing the data I can't confirm, but I would assume those two ESMTypes have different intProfileItemID's?
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
January 29, 2018 at 11:16 am
Hello there, I'm sad if this post is somewhat long, I am attempting to incorporate just significant data. At present I have a content sending out the substance of a brief table to XML, be that as it may one of the kid hubs is making the parent hub copy, I might want to consolidate them into a solitary hub.
January 29, 2018 at 11:55 am
stephenhawking616 - Monday, January 29, 2018 11:16 AMHello there, I'm sad if this post is somewhat long, I am attempting to incorporate just significant data. At present I have a content sending out the substance of a brief table to XML, be that as it may one of the kid hubs is making the parent hub copy, I might want to consolidate them into a solitary hub.
Stephen Hawking is an author at Essay Corp. Stephen is a substance and scholarly author He adores to help understudies with their assignments Do my Essay Now. He composed many books for understudies which propel understudies to accomplish their point throughout everyday life.
So, basically, you allow students to cheat by writing essays that are original by you, therefore not identified as plagiarism, yet still not written by themselves. Fantastic. I hope your day is full of stubbed toes and broken glasses, late planes, trains and automobiles, and that your karma catches up with you as soon as possible.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy