XML path and XML format

• bma234

  Valued Member

  Points: 67

  More actions

  October 31, 2018 at 7:33 am

  #376148

  I have raw data as below in a table

  AgentID               code                     date                         reason
  001                         Not Ready        1/1/2018 9:00 am     break
  001                         Available          1/1/2018 9:05 am      Ready
  001                         Not Ready       1/1/2108 11:00 am    lunch
  002                         Not Ready       1/1/2018 9:00 am      break

  I need xml format with xml path as below. I need your help.

  <AgentDataWork>
  <AgentID> 001 </AgentID>
  <Event>
                  <code>Not Ready</code>
                  <date>1/1/2018 9:00 am   </date>
                  <reason> break</reason>
  </Event>
  <Event>
                  <code> Available     </code>
                  <date>1/1/2018 9:05 am     </date>
                  <reason> Ready </reason>
  </Event>
  <Event>
                  <code> Not Ready       </code>
                  <date>1/1/2108 11:00 am  </date>
                  <reason> lunch </reason>
  </Event>
  </AgentDataWork>
  <AgentDataWork>
  <AgentID> 002 </AgentID>
  <Event>
                  <code> Not Ready       </code>
                  <date>1/1/2018 9:00 am    </date>
                  <reason> break </reason>
  </Event>
  </AgentDataWork>

  Thank you,
  Mahesh

   

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 31, 2018 at 7:56 am

  #2011845

  Whataattempts have you made so far to get his result? Can you post them and tell us what was wrong with them?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• bma234

  Valued Member

  Points: 67

  More actions

  October 31, 2018 at 8:45 am

  #2011865

  USE tempdb
  CREATE TABLE dbo.Agent(AgentID varchar(5),code varchar(20),date varchar(20),reason varchar(10))
  insert into dbo.Agent
  select '001','Not Ready','1/1/2018 9:00 am', 'break' union
  select '001','Available', '1/1/2018 9:05 am', 'Ready' union
  select '001','Not Ready','1/1/2108 11:00 am','lunch' union
  select '002','Not Ready','1/1/2018 9:00 am', 'break'

  select * from dbo.Agent

  SELECT
                      (
                          SELECT     minint.AgentID
                                  ,A.code
                                  ,A.date
                                  ,A.reason
                          FROM dbo.Agent A (NOLOCK) left join (select AgentID,min(date) as MinIntervalStart
                           from dbo.Agent
                          group by AgentID) as minint on A.AgentID = minint.AgentID
                          and a.date = minint.MinIntervalStart
                          ORDER BY a.AgentID,a.date
                          FOR XML PATH('Event'),TYPE
                      )
                      FOR XML PATH('AgentDataWork'),TYPE

  I got below result for above query

  <AgentDataWork>
  <Event>
    <AgentID>001</AgentID>
    <code>Not Ready</code>
    <date>1/1/2018 9:00 am</date>
    <reason>break</reason>
  </Event>
  <Event>
    <code>Available</code>
    <date>1/1/2018 9:05 am</date>
    <reason>Ready</reason>
  </Event>
  <Event>
    <code>Not Ready</code>
    <date>1/1/2108 11:00 am</date>
    <reason>lunch</reason>
  </Event>
  <Event>
    <AgentID>002</AgentID>
    <code>Not Ready</code>
    <date>1/1/2018 9:00 am</date>
    <reason>break</reason>
  </Event>
  </AgentDataWork>

  AgentID tag should come only once after AgentDataWork tag for each agent and following tags should be event tags for that agent.

  Second agent details have to start with only one AgentDataWork tag and only one AgentID tag at the beginning of the agent details and following tags should be event tags.

  but in my results above, AgentID tag is repeating in each Event tag. The AgentID tag should come only once after AgentDataWork for each agent.

  My expected results should be as below

  <AgentDataWork>
      <AgentID>001</AgentID>
      <Event>
          <code>Not Ready</code>
          <date>1/1/2018 9:00 am</date>
          <reason>break</reason>
      </Event>
      <Event>
          <code>Available</code>
          <date>1/1/2018 9:05 am</date>
          <reason>Ready</reason>
      </Event>
      <Event>
          <code>Not Ready</code>
          <date>1/1/2108 11:00 am</date>
          <reason>lunch</reason>
      </Event>
  </AgentDataWork>
  <AgentDataWork>
      <Event>
          <AgentID>002</AgentID>
          <code>Not Ready</code>
          <date>1/1/2018 9:00 am</date>
          <reason>break</reason>
      </Event>
  </AgentDataWork>

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 31, 2018 at 8:52 am

  #2011869

  Thanks for posting your attempt. While I have a look, whya re you storing dates as a varchar? datetimes should be stored as a datetime(2).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 31, 2018 at 9:06 am

  #2011872

  just having a look at that xml further, it isn't valid. You have no root node (AgentDataWork isn't your root, as it's repeated). What is this going to be consumed by?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• bma234

  Valued Member

  Points: 67

  More actions

  October 31, 2018 at 9:07 am

  #2011873

  Hi Thom,

  Please ignore sorting.

  Thank you

• bma234

  Valued Member

  Points: 67

  More actions

  October 31, 2018 at 9:11 am

  #2011874

  Assume that there is other xyz varchar column instead of date column. My requirement is: I should display only one agentid tag and agentdatawork tag for each agent

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 31, 2018 at 9:14 am

  #2011878

  Triple post (sorry). it's also inconsistant. See my notes below:
  <AgentDataWork>
<AgentID>001</AgentID>--Agent ID outside of Event
<Event>
  <code>Not Ready</code>
  <date>1/1/2018 9:00 am</date>
  <reason>break</reason>
</Event>
<Event>
  <code>Available</code>
  <date>1/1/2018 9:05 am</date>
  <reason>Ready</reason>
</Event>
<Event>
  <code>Not Ready</code>
  <date>1/1/2108 11:00 am</date>
  <reason>lunch</reason>
</Event>
</AgentDataWork>
<AgentDataWork>
<Event>
  <AgentID>002</AgentID> --AgentID inside Event
  <code>Not Ready</code>
  <date>1/1/2018 9:00 am</date>
  <reason>break</reason>
</Event>
</AgentDataWork>

  Assuming that AgentID should always in outside the event, then this gets you the result you want:

  --Fix the datatype

  ALTER TABLE dbo.Agent ALTER COLUMN [date] datetime2(0);

  GO

  SELECT *

  FROM dbo.Agent;

  GO

  SELECT AgentID,

     (SELECT sq.code,

        CONVERT(varchar(10),[date],103) + ' ' + STUFF(RIGHT(REPLACE(CONVERT(varchar(20),[date],100),' ','0'),7),6,0,' ') AS [date],

         sq.reason

        FROM dbo.Agent sq

  WHERE sq.AgentID = A.AgentID

         FOR XML PATH('Event'),TYPE)

  FROM dbo.Agent A

  GROUP BY AgentID

  FOR XML PATH('AgentDataWork');

  Note that I also fix the datatype for your date column, as it was being stored as a varchar.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• bma234

  Valued Member

  Points: 67

  More actions

  October 31, 2018 at 10:03 am

  #2011896

  Thank you very much for your help Thom

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  October 31, 2018 at 10:05 am

  #2011897

  bma234 - Wednesday, October 31, 2018 8:45 AM

  USE tempdb
  CREATE TABLE dbo.Agent(AgentID varchar(5),code varchar(20),date varchar(20),reason varchar(10))
  insert into dbo.Agent
  select '001','Not Ready','1/1/2018 9:00 am', 'break' union
  select '001','Available', '1/1/2018 9:05 am', 'Ready' union
  select '001','Not Ready','1/1/2108 11:00 am','lunch' union
  select '002','Not Ready','1/1/2018 9:00 am', 'break'

  select * from dbo.Agent

  SELECT
                      (
                          SELECT     minint.AgentID
                                  ,A.code
                                  ,A.date
                                  ,A.reason
                          FROM dbo.Agent A (NOLOCK) left join (select AgentID,min(date) as MinIntervalStart
                           from dbo.Agent
                          group by AgentID) as minint on A.AgentID = minint.AgentID
                          and a.date = minint.MinIntervalStart
                          ORDER BY a.AgentID,a.date
                          FOR XML PATH('Event'),TYPE
                      )
                      FOR XML PATH('AgentDataWork'),TYPE

  I got below result for above query

  <AgentDataWork>
  <Event>
    <AgentID>001</AgentID>
    <code>Not Ready</code>
    <date>1/1/2018 9:00 am</date>
    <reason>break</reason>
  </Event>
  <Event>
    <code>Available</code>
    <date>1/1/2018 9:05 am</date>
    <reason>Ready</reason>
  </Event>
  <Event>
    <code>Not Ready</code>
    <date>1/1/2108 11:00 am</date>
    <reason>lunch</reason>
  </Event>
  <Event>
    <AgentID>002</AgentID>
    <code>Not Ready</code>
    <date>1/1/2018 9:00 am</date>
    <reason>break</reason>
  </Event>
  </AgentDataWork>

  AgentID tag should come only once after AgentDataWork tag for each agent and following tags should be event tags for that agent.

  Second agent details have to start with only one AgentDataWork tag and only one AgentID tag at the beginning of the agent details and following tags should be event tags.

  but in my results above, AgentID tag is repeating in each Event tag. The AgentID tag should come only once after AgentDataWork for each agent.

  My expected results should be as below

  <AgentDataWork>
      <AgentID>001</AgentID>
      <Event>
          <code>Not Ready</code>
          <date>1/1/2018 9:00 am</date>
          <reason>break</reason>
      </Event>
      <Event>
          <code>Available</code>
          <date>1/1/2018 9:05 am</date>
          <reason>Ready</reason>
      </Event>
      <Event>
          <code>Not Ready</code>
          <date>1/1/2108 11:00 am</date>
          <reason>lunch</reason>
      </Event>
  </AgentDataWork>
  <AgentDataWork>
      <Event>
          <AgentID>002</AgentID>
          <code>Not Ready</code>
          <date>1/1/2018 9:00 am</date>
          <reason>break</reason>
      </Event>
  </AgentDataWork>

  Here's a query that will produce your results, but be aware, as Thom commented, you are storing dates as varchar(20), which is a bad idea.   Thus I chose to standardize your date format with all those dates....  (and you may have a typo with the date in year 2108...  probably was supposed to be 2018):
  CREATE TABLE #Agent (
    AgentID varchar(5),
    code varchar(20),
    [date] varchar(20),
    reason varchar(10)
);
INSERT INTO #Agent (AgentID, code, [date], reason)
SELECT '001','Not Ready','01/01/2018 09:00 am', 'break' UNION ALL
SELECT '001','Available','01/01/2018 09:05 am', 'Ready' UNION ALL
SELECT '001','Not Ready','01/01/2018 11:00 am','lunch' UNION ALL
SELECT '002','Not Ready','01/01/2108 09:00 am', 'break';

  SELECT *
FROM #Agent;

  WITH Agents AS (

      SELECT DISTINCT AgentID
    FROM #Agent
)
SELECT B.AgentID,
    (
    SELECT
        A.code,
        A.[date],
        A.reason
    FROM #Agent AS A (NOLOCK)
    WHERE A.AgentID = B.AgentID
    ORDER BY
        a.AgentID,
        a.[date]
    FOR XML PATH('Event'),TYPE
    )
FROM Agents AS B
FOR XML PATH('AgentDataWork'),TYPE

  DROP TABLE #Agent;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• bma234

  Valued Member

  Points: 67

  More actions

  October 31, 2018 at 10:42 am

  #2011908

  Thank you