XML Data singleton value not fixed????

  • jbalbo

    SSCertifiable

    Points: 7399

    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>










    ?

     

  • Eddie Wuerch

    SSChampion

    Points: 12268

    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

  • jbalbo

    SSCertifiable

    Points: 7399

    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

     

     

  • drew.allen

    SSC Guru

    Points: 76580

    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

  • jbalbo

    SSCertifiable

    Points: 7399

    Thank You

    Eddie and Drew...

    I appreciate the info and the learning experience....

     

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply