Hi
Hope to explain this correctly
I have some XML data that can vary on singleton value
So in the example below I want to list the "Company" along with the "Service" but [1] may be any value('Mental Health', Substance Abuse etc..)If the User entered Mental Health first it would be [1] if they enetered it second it would be [2] etc
I want a column for Mental Health, Substance Abuse, Primary Health, Housing etc.. Company and service
The service part is straight forward
,CASE
WHEN Data.exist('Data/Subtable/ST[Service="Mental Health Clinic"]') = 1
THEN 'Mental Health Clinic'
END AS [Mental Health Clinic]
I was thinking this for the company value , but I dont know how to get around the "[1]"
,CASE
WHEN Data.exist('Data/Subtable/ST[Service="Mental Health Clinic"]') = 1
THEN data.value('(/Data/Subtable/ST/Company)[1]', 'nvarchar(Max)')
end as x
Hope this makes sense...
Thanks
Joe
<Data>
<Subtable>
<ST>
<Service>Mental Health Clinic</Service>
<Company>Mental Health Clinic - Local Mental Health Clinic</Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with contact information for local mental health clinic with walk-in Intake hours.</Notes>
</ST>
<ST>
<Service>Primary health/dental care</Service>
<Company>Medical Assistance - Local primary Doctor</Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with clinic information for xxxx and walk-in hours</Notes>
</ST>
<ST>
<Service>Substance Use Treatment</Service>
<Company>Substance Use Treatment - AA/NA </Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with list of active local AA/NA meetings</Notes>
</ST>
<ST>
<Service>Housing Services</Service>
<Company>Other</Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with contact information for xWorks.</Notes>
</ST>
</Subtable>
</Data>
?
September 18, 2019 at 9:56 pm
You need to shred the XML into a resultset of nodes using the XQuery .nodes method. Then you can address each <ST> block as a single unit.
Note: I added an attribute called ClientID to the <Subtable> element, and duplicated the XML block with different ClientID values to show how to reference a value further up the XML tree.
There's also an additional column called NodeXML that dumps the XML fragment being processed.
DECLARE @x xml =
'<Data>
<Subtable ClientID="001234">
<ST>
<Service>Mental Health Clinic</Service>
<Company>Mental Health Clinic - Local Mental Health Clinic</Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with contact information for local mental health clinic with walk-in Intake hours.</Notes>
</ST>
<ST>
<Service>Primary health/dental care</Service>
<Company>Medical Assistance - Local primary Doctor</Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with clinic information for xxxx and walk-in hours</Notes>
</ST>
<ST>
<Service>Substance Use Treatment</Service>
<Company>Substance Use Treatment - AA/NA </Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with list of active local AA/NA meetings</Notes>
</ST>
<ST>
<Service>Housing Services</Service>
<Company>Other</Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with contact information for xWorks.</Notes>
</ST>
</Subtable>
<Subtable ClientID="002222">
<ST>
<Service>Mental Health Clinic</Service>
<Company>Mental Health Clinic - Local Mental Health Clinic</Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with contact information for local mental health clinic with walk-in Intake hours.</Notes>
</ST>
<ST>
<Service>Primary health/dental care</Service>
<Company>Medical Assistance - Local primary Doctor</Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with clinic information for xxxx and walk-in hours</Notes>
</ST>
<ST>
<Service>Substance Use Treatment</Service>
<Company>Substance Use Treatment - AA/NA </Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with list of active local AA/NA meetings</Notes>
</ST>
<ST>
<Service>Housing Services</Service>
<Company>Other</Company>
<Date_x0020_of_x0020_Referral>2019-08-12T00:00:00-04:00</Date_x0020_of_x0020_Referral>
<Notes>Client provided with contact information for xWorks.</Notes>
</ST>
</Subtable>
</Data>'
SELECT STNodes.ST.value('(../@ClientID)[1]', 'varchar(128)') AS [ClientID],
STNodes.ST.value('(./Service)[1]', 'varchar(128)') AS [Service],
STNodes.ST.value('(./Company)[1]', 'varchar(128)') AS [Company],
STNodes.ST.value('(./Date_x0020_of_x0020_Referral)[1]', 'datetimeoffset') AS [Date of Referral],
STNodes.ST.value('(./Notes)[1]', 'varchar(1024)') AS [Notes],
STNodes.ST.query('.') AS [NodeXML]
FROM @x.nodes('/Data/Subtable/ST') AS STNodes(ST)
Eddie Wuerch
MCM: SQL
Hi Eddie
Thanks so much, I get the logic, but not to sound dumb...
How do I incorporate this into a select statement ?
where 'data' is coming from xdoc table
FROM data.dbo.CalendarEvents AS ce
INNER JOIN data.dbo.ObjectMetadata AS OM2 ON ce.ParentObject = OM2.ObjectID
INNER JOIN data.dbo.ClientCases AS cc2 ON OM2.[Case] = cc2.ObjectID
INNER JOIN data.dbo.XDocuments AS xdoc ON xdoc.ObjectID = OM2.ObjectID
Thanks
Joe
I assume I would write out to a temp table ?
I
September 19, 2019 at 4:20 pm
You would use a CROSS APPLY or OUTER APPLY like any other table-valued function.
FROM data.dbo.CalendarEvents AS ce
INNER JOIN data.dbo.ObjectMetadata AS OM2
ON ce.ParentObject = OM2.ObjectID
INNER JOIN data.dbo.ClientCases AS cc2
ON OM2.[Case] = cc2.ObjectID
INNER JOIN data.dbo.XDocuments AS xdoc
ON xdoc.ObjectID = OM2.ObjectID
CROSS APPLY xdoc.yourfieldnamehere.nodes('/Data/Subtable/ST') AS STNodes(ST)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 19, 2019 at 5:26 pm
Thank You
Eddie and Drew...
I appreciate the info and the learning experience....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply