Dashboard SQL query for UserInputs XML data

Radek_Novak

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?

Radek_Novak

repeat to myself

SELECT

  [ID],

  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,

  [Statustext]

FROM

  (

    SELECT 

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'

    WHERE 

      SR.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 Like 'Something in title %'

  ) AS Data

-- CSY is my locale