SQL server XML Parsing to Table

Question

I am new to SQL xml operations. I have a problem with SQL & XML Parsing.

I have a Table named Task. It has 3 columns:

Name
Task
Date

Task is saving in Hours worked as an xml in the following format:

<TASK>
    <A>1</A>
    <B>4</B>
    <C>0</C>
</TASK>

My table will look like this (with multiple employees)

Name          |   Task                                     | Date
========================================================================
Employee2219  |   <TASK><A>1</A><B>4</B><C>0</C></ TASK >  | 2014-01-28
Employee2219  |   <TASK><A>3</A><B>2</B><C>1</C></ TASK >  | 2014-01-29
....

I need to calculate the hours spend per employee for each task in a week like:

Name          | A | B | C | D 
===============================
Employee2219  | 4 | 6 | 1 | 0 
....

I am new to XML and I could not work this out.

Is the `Task` column the actual XML data type or some CHAR variant? — Bryan, Jan 29 '14 at 13:56

StuartLC · Accepted Answer · 2014-01-30T04:40:53.777

2

It is straightforward to extract the xml using XQuery:

SELECT 
    Name, 
    SUM(TaskA) AS ATotal, 
    SUM(TaskB) AS BTotal, 
    SUM(TaskC) AS CTotal, 
    SUM(TaskD) AS DTotal
FROM
(
    SELECT
      t.NAME,
      t.Date,
      Nodes.node.value('(A)[1]', 'int') AS TaskA,
      Nodes.node.value('(B)[1]', 'int') AS TaskB,
      Nodes.node.value('(C)[1]', 'int') AS TaskC,
      Nodes.node.value('(D)[1]', 'int') AS TaskD
    FROM
        TASK t
        cross apply t.Task.nodes('/TASK') as Nodes(node)
) x
WHERE
    [Date] BETWEEN '2014-01-01' AND '2014-01-07' -- Your week range
GROUP BY 
    Name;

SqlFiddle here

Please post your question in text - many corporate SO users are blocked from sites like imgur, so answering becomes guess work.

edited Jan 30 '14 at 04:40

answered Jan 29 '14 at 14:02

StuartLC

104,537
17
209
285

will this also sum up the values for each employee? – Tanner Jan 29 '14 at 14:04
basically name is employee id and it can appear multiple times so the task xml needs to be summed for each employee id... but yes OP is not great – Tanner Jan 29 '14 at 14:11
Thank You so much StuartLC.. It is very Helpfull.. We could not predit the task number.. it may change.. what can we do in that case..? in that case we could not able to specify each Task.. – Prince Joseph Jan 29 '14 at 14:12
@StuartLC too late for you I see now – Tanner Jan 29 '14 at 14:19
@StuartLC never seen this done before, mixing sql and XML like that but great answer, will keep it in mind for the future – Tanner Jan 29 '14 at 14:25
@user3249023 Your requirement is growing rather tricky. If you don't know what the elements are called in the xml, you are going to need to use 'Task/*' and probably also project out `local-name`, and then do a dynamic pivot - see here. http://stackoverflow.com/a/21062092/314291 – StuartLC Jan 29 '14 at 14:47

SQL server XML Parsing to Table

1 Answers1