Need help on extracting to XML format - Solved

• dcw8888

  SSC Eights!

  Points: 885

  More actions

  October 18, 2017 at 11:39 am

  #324207

  Hi,

  I have the following table:

  CREATE TABLE dbo.TimeTable (
  WorkDate date,
  EmpID int,
  EmployeeName Varchar(20),
  Code Varchar(20),
  Starttime time,
  Endtime time
);
INSERT INTO TimeTable (WorkDate, EmpID, EmployeeName, Code, Starttime, Endtime)
  VALUES  ('6/19/2017', 15, 'Tammy', 'ECDISP', '13:00:00', '20:30:00'),
    ('6/19/2017', 15, 'Tammy', 'Break', '20:30:00', '21:00:00'),
    ('6/19/2017', 15, 'Tammy', 'Lunch', '21:00:00', '22:00:00'),
    ('6/19/2017', 15, 'Tammy', 'ECDISP', '22:00:00', '01:00:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Open', '7:00:00', '10:45:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Break', '10:45:00', '11:15:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Lunch', '11:15:00', '11:30:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Open', '11:30:00', '15:00:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Lunch', '15:00:00', '15:45:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Open', '15:45:00', '19:00:00');

  Running the following statement:
  select * from timetable
  FOR XML RAW ('EmployeePunch'), ROOT ('EmployeePunchImport'), ELEMENTS XSINIL;

  Gives me this result.
  <EmployeePunchImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>13:00:00</Starttime>
    <Endtime>20:30:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Break</Code>
    <Starttime>20:30:00</Starttime>
    <Endtime>21:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>21:00:00</Starttime>
    <Endtime>22:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>22:00:00</Starttime>
    <Endtime>01:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>07:00:00</Starttime>
    <Endtime>10:45:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Break</Code>
    <Starttime>10:45:00</Starttime>
    <Endtime>11:15:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>11:15:00</Starttime>
    <Endtime>11:30:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>11:30:00</Starttime>
    <Endtime>15:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>15:00:00</Starttime>
    <Endtime>15:45:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>15:45:00</Starttime>
    <Endtime>19:00:00</Endtime>
   </EmployeePunch>
  </EmployeePunchImport>

  However, I want to have another XML tag inserted to the XML extract.
  <Settings>
       <XrefCodeValidationLevel>STRICT</XrefCodeValidationLevel>
  </Settings>

  Final Result:
  <EmployeePunchImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Settings>
       <XrefCodeValidationLevel>STRICT</XrefCodeValidationLevel>
  </Settings>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>13:00:00</Starttime>
    <Endtime>20:30:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Break</Code>
    <Starttime>20:30:00</Starttime>
    <Endtime>21:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>21:00:00</Starttime>
    <Endtime>22:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>22:00:00</Starttime>
    <Endtime>01:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>07:00:00</Starttime>
    <Endtime>10:45:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Break</Code>
    <Starttime>10:45:00</Starttime>
    <Endtime>11:15:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>11:15:00</Starttime>
    <Endtime>11:30:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>11:30:00</Starttime>
    <Endtime>15:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>15:00:00</Starttime>
    <Endtime>15:45:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>15:45:00</Starttime>
    <Endtime>19:00:00</Endtime>
  </EmployeePunch>
  </EmployeePunchImport>

  Can anyone please help me how to add that Settings XML tag?

  Many thanks!

  I have figured out a solution.  Here is the codes.

  Declare @XML XML

  SET @XML =
(Select * from timetable
FOR XML RAW ('EmployeePunch'), ROOT ('EmployeePunchImport'), ELEMENTS XSINIL)

  SET @XML.modify('insert <Settings><XrefCodeValidationLevel>Strict</XrefCodeValidationLevel></Settings> before (/EmployeePunchImport/EmployeePunch)[1]')

  select @XML

• TheCTEGuy

  Ten Centuries

  Points: 1032

  More actions

  October 24, 2017 at 5:10 am

  #1964921

  dcw8888 - Wednesday, October 18, 2017 11:39 AM

  Hi,

  I have the following table:

  CREATE TABLE dbo.TimeTable (
  WorkDate date,
  EmpID int,
  EmployeeName Varchar(20),
  Code Varchar(20),
  Starttime time,
  Endtime time
);
INSERT INTO TimeTable (WorkDate, EmpID, EmployeeName, Code, Starttime, Endtime)
  VALUES  ('6/19/2017', 15, 'Tammy', 'ECDISP', '13:00:00', '20:30:00'),
    ('6/19/2017', 15, 'Tammy', 'Break', '20:30:00', '21:00:00'),
    ('6/19/2017', 15, 'Tammy', 'Lunch', '21:00:00', '22:00:00'),
    ('6/19/2017', 15, 'Tammy', 'ECDISP', '22:00:00', '01:00:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Open', '7:00:00', '10:45:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Break', '10:45:00', '11:15:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Lunch', '11:15:00', '11:30:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Open', '11:30:00', '15:00:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Lunch', '15:00:00', '15:45:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Open', '15:45:00', '19:00:00');

  Running the following statement:
  select * from timetable
  FOR XML RAW ('EmployeePunch'), ROOT ('EmployeePunchImport'), ELEMENTS XSINIL;

  Gives me this result.
  <EmployeePunchImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>13:00:00</Starttime>
    <Endtime>20:30:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Break</Code>
    <Starttime>20:30:00</Starttime>
    <Endtime>21:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>21:00:00</Starttime>
    <Endtime>22:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>22:00:00</Starttime>
    <Endtime>01:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>07:00:00</Starttime>
    <Endtime>10:45:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Break</Code>
    <Starttime>10:45:00</Starttime>
    <Endtime>11:15:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>11:15:00</Starttime>
    <Endtime>11:30:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>11:30:00</Starttime>
    <Endtime>15:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>15:00:00</Starttime>
    <Endtime>15:45:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>15:45:00</Starttime>
    <Endtime>19:00:00</Endtime>
   </EmployeePunch>
  </EmployeePunchImport>

  However, I want to have another XML tag inserted to the XML extract.
  <Settings>
       <XrefCodeValidationLevel>STRICT</XrefCodeValidationLevel>
  </Settings>

  Final Result:
  <EmployeePunchImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Settings>
       <XrefCodeValidationLevel>STRICT</XrefCodeValidationLevel>
  </Settings>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>13:00:00</Starttime>
    <Endtime>20:30:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Break</Code>
    <Starttime>20:30:00</Starttime>
    <Endtime>21:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>21:00:00</Starttime>
    <Endtime>22:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>22:00:00</Starttime>
    <Endtime>01:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>07:00:00</Starttime>
    <Endtime>10:45:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Break</Code>
    <Starttime>10:45:00</Starttime>
    <Endtime>11:15:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>11:15:00</Starttime>
    <Endtime>11:30:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>11:30:00</Starttime>
    <Endtime>15:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>15:00:00</Starttime>
    <Endtime>15:45:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>15:45:00</Starttime>
    <Endtime>19:00:00</Endtime>
  </EmployeePunch>
  </EmployeePunchImport>

  Can anyone please help me how to add that Settings XML tag?

  Many thanks!

  I have figured out a solution.  Here is the codes.

  Declare @XML XML

  SET @XML =
(Select * from timetable
FOR XML RAW ('EmployeePunch'), ROOT ('EmployeePunchImport'), ELEMENTS XSINIL)

  SET @XML.modify('insert <Settings><XrefCodeValidationLevel>Strict</XrefCodeValidationLevel></Settings> before (/EmployeePunchImport/EmployeePunch)[1]')

  select @XML

  Yes. This seems to be best sol.

  First solve the problem then write the code !

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  October 24, 2017 at 5:20 pm

  #1965033

  dcw8888 - Wednesday, October 18, 2017 11:39 AM

  Hi,

  I have the following table:

  CREATE TABLE dbo.TimeTable (
  WorkDate date,
  EmpID int,
  EmployeeName Varchar(20),
  Code Varchar(20),
  Starttime time,
  Endtime time
);
INSERT INTO TimeTable (WorkDate, EmpID, EmployeeName, Code, Starttime, Endtime)
  VALUES  ('6/19/2017', 15, 'Tammy', 'ECDISP', '13:00:00', '20:30:00'),
    ('6/19/2017', 15, 'Tammy', 'Break', '20:30:00', '21:00:00'),
    ('6/19/2017', 15, 'Tammy', 'Lunch', '21:00:00', '22:00:00'),
    ('6/19/2017', 15, 'Tammy', 'ECDISP', '22:00:00', '01:00:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Open', '7:00:00', '10:45:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Break', '10:45:00', '11:15:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Lunch', '11:15:00', '11:30:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Open', '11:30:00', '15:00:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Lunch', '15:00:00', '15:45:00'),
    ('6/19/2017', 24, 'Dalstrom', 'Open', '15:45:00', '19:00:00');

  Running the following statement:
  select * from timetable
  FOR XML RAW ('EmployeePunch'), ROOT ('EmployeePunchImport'), ELEMENTS XSINIL;

  Gives me this result.
  <EmployeePunchImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>13:00:00</Starttime>
    <Endtime>20:30:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Break</Code>
    <Starttime>20:30:00</Starttime>
    <Endtime>21:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>21:00:00</Starttime>
    <Endtime>22:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>22:00:00</Starttime>
    <Endtime>01:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>07:00:00</Starttime>
    <Endtime>10:45:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Break</Code>
    <Starttime>10:45:00</Starttime>
    <Endtime>11:15:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>11:15:00</Starttime>
    <Endtime>11:30:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>11:30:00</Starttime>
    <Endtime>15:00:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>15:00:00</Starttime>
    <Endtime>15:45:00</Endtime>
   </EmployeePunch>
   <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>15:45:00</Starttime>
    <Endtime>19:00:00</Endtime>
   </EmployeePunch>
  </EmployeePunchImport>

  However, I want to have another XML tag inserted to the XML extract.
  <Settings>
       <XrefCodeValidationLevel>STRICT</XrefCodeValidationLevel>
  </Settings>

  Final Result:
  <EmployeePunchImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Settings>
       <XrefCodeValidationLevel>STRICT</XrefCodeValidationLevel>
  </Settings>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>13:00:00</Starttime>
    <Endtime>20:30:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Break</Code>
    <Starttime>20:30:00</Starttime>
    <Endtime>21:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>21:00:00</Starttime>
    <Endtime>22:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>15</EmpID>
    <EmployeeName>Tammy</EmployeeName>
    <Code>ECDISP</Code>
    <Starttime>22:00:00</Starttime>
    <Endtime>01:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>07:00:00</Starttime>
    <Endtime>10:45:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Break</Code>
    <Starttime>10:45:00</Starttime>
    <Endtime>11:15:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>11:15:00</Starttime>
    <Endtime>11:30:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>11:30:00</Starttime>
    <Endtime>15:00:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Lunch</Code>
    <Starttime>15:00:00</Starttime>
    <Endtime>15:45:00</Endtime>
  </EmployeePunch>
  <EmployeePunch>
    <WorkDate>2017-06-19</WorkDate>
    <EmpID>24</EmpID>
    <EmployeeName>Dalstrom</EmployeeName>
    <Code>Open</Code>
    <Starttime>15:45:00</Starttime>
    <Endtime>19:00:00</Endtime>
  </EmployeePunch>
  </EmployeePunchImport>

  Can anyone please help me how to add that Settings XML tag?

  Many thanks!

  I have figured out a solution.  Here is the codes.

  Declare @XML XML

  SET @XML =
(Select * from timetable
FOR XML RAW ('EmployeePunch'), ROOT ('EmployeePunchImport'), ELEMENTS XSINIL)

  SET @XML.modify('insert <Settings><XrefCodeValidationLevel>Strict</XrefCodeValidationLevel></Settings> before (/EmployeePunchImport/EmployeePunch)[1]')

  select @XML

  Care to share the reference you used to figure it out?  Thanks.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• TheCTEGuy

  Ten Centuries

  Points: 1032

  More actions

  October 24, 2017 at 8:35 pm

  #1965045

  @jeff - I hope this link will help - https://docs.microsoft.com/en-us/sql/t-sql/xml/insert-xml-dml

  First solve the problem then write the code !