December 13, 2016 at 2:38 pm
Hi
I'm trying to sum up an XML(XML DATA BELOW) field based on if conditions apply
so in my case, the XML tag of "PROMTN"(where N = 1 - 12 )
Is either "Collateral" or "Direct" I want to end up with a sum for Direct and a sum for collateral
I've got something like this going, but not getting anywhere....
Thx
Joe
SELECT dbo.XDocuments.ObjectID
,CASE
WHEN Data.value('(/Data/Prompt1)[1]', 'nvarchar(10)') ='Collateral'
AND Data.value('(/Data/Crisis_x0020_Assessment)[1]', 'nvarchar(10)') ='TRUE'
then SUM(cast(Data.value('(/Data/Crisis_x0020_Assessment_x0020_Minutes)[1]', 'int')AS INT)) END AS TEST
FROM XDocuments INNER JOIN
ObjectMetadata AS om INNER JOIN
CalendarEvents ON om.ObjectID = CalendarEvents.ParentObject ONXDocuments.ObjectID = om.ObjectID
WHERE ...
GROUP BY .....
XML DATA
<Data>
<Total_x0020_Minutes>78</Total_x0020_Minutes>
<Day>1</Day>
<Contact_x0020_Type>In-Person Meeting</Contact_x0020_Type>
<Functioning>TEST</Functioning>
<Crisis_x0020_Assessment>true</Crisis_x0020_Assessment>
<Prompt1>Collateral</Prompt1>
<Crisis_x0020_Assessment_x0020_Minutes>1</Crisis_x0020_Assessment_x0020_Minutes>
<Crisis_x0020_Assessment_x0020_Details>Crisis Assessment</Crisis_x0020_Assessment_x0020_Details>
<Tx_x0020_plan>true</Tx_x0020_plan>
<Prompt2>Direct</Prompt2>
<Tx_x0020_Plan_x0020_Minutes>2</Tx_x0020_Plan_x0020_Minutes>
<Tx_x0020_Plan_x0020_Details>Treatment Plan/ Team Meeting:</Tx_x0020_Plan_x0020_Details>
<Risk_x0020_Management_x002F_Safety_x0020_Plan>true</Risk_x0020_Management_x002F_Safety_x0020_Plan>
<Prompt3>Collateral</Prompt3>
<Minutes3>3</Minutes3>
<Details3>Risk Management/Safety Plan:</Details3>
<Consult_x0020_with_x0020_Supervisor>true</Consult_x0020_with_x0020_Supervisor>
<Prompt4>Direct</Prompt4>
<Minutes4>4</Minutes4>
<Details4>Consult with Supervisor:</Details4>
<Consult_x0020_with_x0020_MD>true</Consult_x0020_with_x0020_MD>
<Prompt5>Collateral</Prompt5>
<Minutes5>5</Minutes5>
<Details5>Consult with MD:</Details5>
<Documentation>true</Documentation>
<Prompt6>Direct</Prompt6>
<Minutes6>6</Minutes6>
<Details6>Documentation:</Details6>
<Telephone_x0020_Support>true</Telephone_x0020_Support>
<Prompt7>Collateral</Prompt7>
<Minutes7>7</Minutes7>
<Details7>Telephone Support:</Details7>
<Collateral_x0020_Contact_x002F_Coordination>true</Collateral_x0020_Contact_x002F_Coordination>
<Prompt8>Direct</Prompt8>
<Minutes8>8</Minutes8>
<Details8>Collateral Contact/Coordination:</Details8>
<Referral_x002F_linkages>true</Referral_x002F_linkages>
<Prompt9>Collateral</Prompt9>
<Minutes9>9</Minutes9>
<Details9>Referral/linkages:</Details9>
<Family_x0020_Psycho-Education>true</Family_x0020_Psycho-Education>
<Prompt10>Direct</Prompt10>
<Minutes10>10</Minutes10>
<Details10>Family Psycho-Education:</Details10>
<Parent_x0020_Support>true</Parent_x0020_Support>
<Prompt11>Collateral</Prompt11>
<Minutes11>11</Minutes11>
<Details11>Parent Support:</Details11>
<After_x0020_Care_x0020_Services>true</After_x0020_Care_x0020_Services>
<Prompt12>Direct</Prompt12>
<Minutes12>12</Minutes12>
<Details12>After Care Services:</Details12>
December 13, 2016 at 3:05 pm
Your XML is incomplete.
I think you have the wrong scope for your CASE/SUM statement. You have your SUM inside your CASE when you probably want your CASE inside your SUM.
SELECT dbo.XDocuments.ObjectID
, SUM(CASE
WHEN Data.value('(/Data/Prompt1)[1]', 'nvarchar(10)') ='Collateral'
AND Data.value('(/Data/Crisis_x0020_Assessment)[1]', 'nvarchar(10)') ='TRUE'
then cast(Data.value('(/Data/Crisis_x0020_Assessment_x0020_Minutes)[1]', 'int')AS INT) END) AS TEST
FROM XDocuments INNER JOIN
ObjectMetadata AS om ON XDocuments.ObjectID = om.ObjectID -- On clause should be here instead of later.
INNER JOIN
CalendarEvents ON om.ObjectID = CalendarEvents.ParentObject -- ON XDocuments.ObjectID = om.ObjectID moved up to customary position.
WHERE ...
GROUP BY .....
Also, it's usually customary to have the ON clause immediately following the JOIN clause that it's associated with unless you are mixing outer and inner joins and you need to make sure that the inner joins are evaluated before the outer joins. Since you only have inner joins, there is no reason to use an unusual ordering of your ON clauses.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 13, 2016 at 3:11 pm
Hi Drew,
Thanks so much for the info..
I need to go over it for me to get a better understanding (Having a hard time grasping XML fields...lol)
So I may be back with questions... Thanks Again
December 13, 2016 at 3:24 pm
Hi Drew,
Gave it a try getting "The XML data type cannot be compared or sorted, except when using the IS NULL operator."
Read something about having to convert the XML field?
Tried below but still getting the same error
SELECT dbo.XDocuments.ObjectID
, SUM(CASE
WHEN Data.value('(/Data/Prompt1)[1]', 'nvarchar(10)') ='Collateral'
AND Data.value('(/Data/Crisis_x0020_Assessment)[1]', 'nvarchar(10)') ='TRUE'
then cast(Data.value('(/Data/Crisis_x0020_Assessment_x0020_Minutes)[1]', 'int')AS INT) END) AS TEST
FROM XDocuments INNER JOIN
ObjectMetadata AS om ON XDocuments.ObjectID = om.ObjectID -- On clause should be here instead of later.
INNER JOIN
CalendarEvents ON om.ObjectID = CalendarEvents.ParentObject -- ON XDocuments.ObjectID = om.ObjectID moved up to customary position.
WHERE (dbo.CalendarEvents.Subject = 'Note')
GROUP BY XDocuments.ObjectID, convert(xml,convert(varchar(max),XDocuments.Data))
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply