Home Self-Service Portal - Community

Dashboard SQL query for UserInputs XML data

Hello, we have a request to create a dashboard, which, in addition to the standard information about the request, would also contain information that is the content of answers to questions in the form - UserInputs.

Example: e.g. business group number, list of places that are in the request as a selection list.

Is there a way to take and parse this xml data within a dashboard query?


  • Options
    Radek_NovakRadek_Novak Member IT Monkey ✭

    repeat to myself



      UserInputXml.value('(UserInputs/UserInput[@Question="Locality"]/@Answer)[1]', 'VARCHAR(MAX)') AS Locality,

    UserInputXml.value('(UserInputs/UserInput[@Question="Cost Center"]/@Answer)[1]', 'VARCHAR(MAX)') AS CostCenter,

      UserInputXml.value('(UserInputs/UserInput[@Question="Supplier"]/@Answer)[1]', 'VARCHAR(MAX)') AS Supplier,





    CAST(UserInput_75DF0BE8_3446_F616_6AC0_8510B74838AF AS XML) AS UserInputXml, 

    SR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as [ID], 

    SR.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as [Title],

      CAST(st.DisplayString AS nvarchar) as [Statustext],

    CAST(wi.WorkItemId AS nvarchar) AS [Identifikator]

    FROM [ServiceManager].[dbo].[MT_System$WorkItem$ServiceRequest] SR

        LEFT JOIN ServiceManagement.dbo.WorkItem   wi ON SR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C=wi.WorkItemId

        LEFT JOIN ServiceManagement.dbo.DisplayString st ON st.ElementID = wi.StatusId AND st.LocaleID = 'CSY'


          SR.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 Like 'Something in title %'

      ) AS Data

    -- CSY is my locale

Sign In or Register to comment.