Kaseya Community

Custom SQL Query with XML

This question is not answered

Hi All, 

Hopefully a quick one for you SQL Query gurus. 

Query:

select * from kasadmin.sdincident

Returns:

NULL NULL 126553577569571 N NULL NULL NULL NULL <CustomFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd"><Field fieldName="Source">Monitoring</Field></CustomFields> 1 NULL

Analysis:

That is fine, from there, i need to query the XML data and return it into one big set. I believe i need to do something with XQUERY but i don't know where. 

My Theory (hopefully this explains what i want better):

select * from kasadmin.sdincident and select * from customFields(whichisxmldata) 

Then it should return: 

data data data xmldata xmldata xmldata xmldata xmldata

Or a real life example of what i want to see from that fake query above:

incidentId,    date,          submitteremail,           xmlUrgency,    xmlassginee,    xmlSource
--------------     -----           ---------------------            ---------------     -----------------      -------------
1234             10/10/10   email@email.com      high                 johnsmith           customercalled

Does this make any sense? Does anyone have a sample query they have done pulling all data out of the service desk + a custom field containing XML?

I am furiously looking around on the internets to figure it out, i know it can be done, and it should be relatively simple. 

Ideas and or samples?

All Replies
  • hi,

    Did you able to figure this as i am looking to do some custom query with service desk and have same problem.

    thanks

  • I don't beleive you need to parse the xml data, see below:

    select t.ref AS IncidentID, t.creation_datetime AS sDate, t.submitter_email_addr AS SubEmailAdd, t.assignee AS AssignedTo, t.sourceType, t.severity

     from kasadmin.vbo_SDIncidents_General t

  • if you NEED to query the xml, use the .value( ) for this

    example....

    select * from kasadmin.sdincident

    WHERE customFields.value(what you looking for) = BLAH BLAH BLAH :D

  • Struggling with this one too.

    We have a customfield in our SD where our techs fill in an integer (billable time per case)

    I'm in desperate need to extract those  values from that field (which we call: SpentTime)  in the customfields XML but can't figure out how.  

    Would be so greatful if you (danrche) or someone else could post an example so that I can understand the syntax of  .value( )  

    (what you looking for) = BLAH BLAH BLAH :D) don't make much sense too me ;)



    [edited by: Johan Wiman at 7:23 AM (GMT -7) on 19 Sep 2012] spelling