get AssignedTo column value from sharepoint tasks list using sql server query

Question

I did SQL server query to retrieve tasks list data but the field AssignedTo not retrieved i don't know why?

My SQL query :

SELECT W.Title [webTitle],
    AD.tp_ID [Task Id],
    tp_columnset.value('(nvarchar1)[1]','nvarchar(200)') as [Task Name],
    tp_columnset.value('(nvarchar5)[1]','nvarchar(50)') as [Task Outcome],
    tp_columnset.value('(nvarchar4)[1]','nvarchar(50)') as [Task Status],
    tp_columnset.value('(ntext1)[1]','nvarchar(200)') as [user comment],
    AD.tp_columnset,
     FROM [WSS_Content].[dbo].[AllUserData] AD 
     INNER JOIN [WSS_Content].[dbo].[AllLists] AL ON AD.[tp_ListId]=AL.tp_ID 
     INNER JOIN [WSS_Content].[dbo].[Webs] W ON W.Id = AL.tp_WebId 
     WHERE AL.tp_Title = 'TasksListName' AND AD.tp_DeleteTransactionId = 0x

The output of the column tp_columnset :

<float1>0.000000000000000e+000</float1>
<ntext3>[{"ItemId":1,"WebId":"073b3d62-fdd3-431e-a6f5-481632b49b91","ListId":"a44ff86b-4ce6-41e7-b43e-331bf7996af1"}]</ntext3>
<nvarchar1>task name</nvarchar1>
<nvarchar3>(2) Normal</nvarchar3>
<nvarchar4>Not Started</nvarchar4>
<nvarchar7>03c30656-f72b-4992-8ea6-7b9097ae8155</nvarchar7>

Answer 1

i did this task using rest api

html code :

 

    <table id="tasksTable" >
                            <tr>
                                <th>Task title</th>
                                <th>Assigned Date</th>
                             
                            </tr>
            </table>

script code :

    <script>
      function getTasks(url, success) {
            $.ajax({
                type: "GET",
                contentType: "application/json; odata=verbose",
                url: url,
                headers: {
                    "accept": "application/json;odata=verbose"
                    
                },          
                dataType: "json",
                success: function (response) {
                    success(response);
                },
                error: function (err) {
                    console.log(" error!");
                }
            });
        }

$(document).ready(function () {

  var currentUserId;
        getTasks("http://localhost/subsite/_api/Web/CurrentUser?$select=Id", function (data) {
           currentUserId = data.d.Id;
        });
 getTasks("http://localhost/subsite/_api/Web/lists/getbytitle('Workflow Tasks')/items? 
$filter=AssignedTo/ID eq " + currentUserId + " and PercentComplete eq 0 ", function (data) 
         {
              $.each(data.d.results, function (i, v) {
                            if (data.d.results.length) {
                                var r = $('<tr><td>' + v.Title + '</td><td>' + v.Created + 
                                '</td></tr>');
                                $('#tasksTable').append(r);
    
                            }
                 });
    
            });
    
    });
    
    </script>