Sql XML Path with different children

Question

Hi, I have done a lot of XML PATH statements, but this one escapes me or might not even be possible with multiple different children.

The end result should look like this

<Process>
<TaskList>
<SqlTask Name="Get Report Parameters">
    <StoredProcName>GetReportParameters</StoredProcName>
        <ConnectionName>Local</ConnectionName>
        <DataTableName>DistributionList</DataTableName>
        <Parameters>
              <Parameter>
            <Name>ReportName</Name>
            <Value>TheReprot</Value>
            <Type>String</Type>
              </Parameter>
        </Parameters>
  </SqlTask>
  <LoopTask Name="Loop Report Creation" ContainerKey="DistributionList">
  <TaskList>
        <ReportTask Name="Report In Loop">   
    </ReportTask>
</TaskList>
  </LoopTask>
  <SqlTask Name="Get Email Addresses">
    <StoredProcName>GetMailingAddress</StoredProcName>
        <ConnectionName>Local</ConnectionName>
        <DataTableName>EmailList</DataTableName>

  </SqlTask>
  <LoopTask Name="Loop Mail Creation" ContainerKey="EmailList">
<TaskList>
        <MailTask Name="Send Email In Loop">       
        </MailTask>
</TaskList>
  </LoopTask>
</TaskList>
</Process>

Below is some test table and data I have so far. The question really is how can I display different child nodes under the same root. And can I derive a tag name from a column value?

CREATE TABLE #TASK (
    TaskId INT IDENTITY(1,1)
,    ProcessId INT
,    TaskType VARCHAR(255)
,    TaskName VARCHAR(255)
,    ContainerKey VARCHAR(255)
,    ParentTaskId INT
)

CREATE TABLE #TASK_PARAMETERS 

(
    TaskId INT
,    Name VARCHAR(255)
,    Value VARCHAR(MAX)
,    [Type] VARCHAR(128)
)

CREATE TABLE #TASK_DETAILS
(
    TaskId INT
,    DetailName VARCHAR(255)
,    DetailValue VARCHAR(MAX)
)

DECLARE @TaskId AS INT
DECLARE @ParentTaskId AS INT


INSERT INTO #TASK 
(
    ProcessId
,    TaskType
,    TaskName
,    ContainerKey
,    ParentTaskId
)
VALUES 
(
0
,    'SqlTask'
,    'Get Report Parameters'
,    NULL
,    NULL
)

SET @TaskId = @@IDENTITY

INSERT INTO #TASK_DETAILS
(
    TaskId
,    DetailName
,    DetailValue
)
VALUES 
(
    @TaskId
,    'StoredProceName'
,    'GetReportParamters'
)

INSERT INTO #TASK_DETAILS
(
    TaskId
,    DetailName
,    DetailValue
)
VALUES 
(
    @TaskId
,    'ConnectionName'
,    'Local'
)

INSERT INTO #TASK_DETAILS
(
    TaskId
,    DetailName
,    DetailValue
)
VALUES 
(
    @TaskId
,    'DataTableName'
,    'DistributionList'
)

INSERT INTO #TASK_PARAMETERS 

(
    TaskId
,    Name
,    Value
,    [Type]
)
VALUES 
(
    @TaskId
,    'ReportName'
,    'TheReprot'
,   'String'
)


INSERT INTO #TASK 
(
    ProcessId
,    TaskType
,    TaskName
,    ContainerKey
,    ParentTaskId
)
VALUES 
(
0
,    'LoopTask'
,    'Loop Report Creation'
,    'DistributionList'
,    NULL
)

SET @ParentTaskId = @@IDENTITY


INSERT INTO #TASK 
(
    ProcessId
,    TaskType
,    TaskName
,    ContainerKey
,    ParentTaskId
)
VALUES 
(
0
,    'ReportTask'
,    'Report In Loop'
,    NULL
,    @ParentTaskId
)


INSERT INTO #TASK 
(
    ProcessId
,    TaskType
,    TaskName
,    ContainerKey
,    ParentTaskId
)
VALUES 
(
0
,    'SqlTask'
,    'Get Email Addresses'
,    NULL
,    NULL
)

SET @TaskId = @@IDENTITY

INSERT INTO #TASK_DETAILS
(
    TaskId
,    DetailName
,    DetailValue
)
VALUES 
(
    @TaskId
,    'StoredProceName'
,    'GetMailingAddress'
)

INSERT INTO #TASK_DETAILS
(
    TaskId
,    DetailName
,    DetailValue
)
VALUES 
(
    @TaskId
,    'ConnectionName'
,    'Local'
)

INSERT INTO #TASK_DETAILS
(
    TaskId
,    DetailName
,    DetailValue
)
VALUES 
(
    @TaskId
,    'DataTableName'
,    'EmailList'
)


INSERT INTO #TASK 
(
    ProcessId
,    TaskType
,    TaskName
,    ContainerKey
,    ParentTaskId
)
VALUES 
(
0
,    'LoopTask'
,    'Loop Mail Creation'
,    'EmailList'
,    NULL
)

SET @ParentTaskId = @@IDENTITY

INSERT INTO #TASK 
(
    ProcessId
,    TaskType
,    TaskName
,    ContainerKey
,    ParentTaskId
)
VALUES 
(
0
,    'MailTask'
,    'Send Email In Loop'
,    NULL
,    @ParentTaskId
)


SELECT *
FROM #TASK

SELECT *
FROM #TASK_PARAMETERS 

SELECT *
FROM  #TASK_DETAILS

Answer 1

Right, you have a multitude of issues to overcome with your sample!

Firstly, I'll give you the answer, although note that in order to process hierarchically correctly it had to be a recursive function, so the test data you provided had to be created in permanent tables rather than temporary (simpler) and then I'll point out a few useful techniques that I used in it to solve the problem.

ALTER FUNCTION GetTasks (@ParentId varchar(255)= NULL) 
RETURNS
XML
BEGIN
DECLARE @ReturnXML XML

SELECT @ReturnXML = 
(
    SELECT
    (
        SELECT 
            CONVERT(XML,
                --Main task start tag
                '<'+master_t.TaskType+' Name="'+master_t.TaskName+'">'+ 
                    CONVERT(VARCHAR(MAX),
                        (

                            SELECT
                            dbo.GetTasks(master_t.TaskId),
                            (
                                SELECT 
                                    CONVERT(XML,'<'+DetailName+'>'+DetailValue+'</'+DetailName+'>')
                                FROM
                                    TASK_DETAILS t 
                                WHERE
                                    TaskId = master_t.TaskId
                                FOR XML PATH(''),Type
                            ),
                            (
                                SELECT Name,Value,Type FROM TASK_PARAMETERS t 
                                WHERE TaskId=master_t.TaskId
                                FOR XML PATH('Parameter'),Type
                            ) 'Parameters'
                            FOR XML PATH(''),Type 
                        )
                    )
                    +
                --Main task end tag
                '</'+master_t.TaskType+'>'
            )
        FROM 
            TASK master_t
        WHERE 
            --Effectively ignore the parentId field if it is not passed.
            ISNULL(ParentTaskId,'') = CASE WHEN @ParentId IS NULL THEN '' ELSE @ParentId END


        FOR XML PATH(''),Type
    ) 'TaskList'  FOR XML PATH(''),Type
) 

RETURN @ReturnXML
END
GO

Call this function like this:

SELECT dbo.GetTasks(NULL)

Right the techniques that I think are worth noting are:

a) You can manually create xml nodes by simply building them from strings- this is useful if the node names are in the table. The only thing you have to be aware of is that to put an open and closing tag around a block, you will probably have to convert the block to a string first, tack on the tags, and then convert the whole thing to xml (piecemeal wont work as the convert-to-xml function would expect you to provide well formed XML.

b) You sometimes have to nest things in brackets to achieve a tag around all the subtags... An example makes this clearer:

 SELECT 
    TaskName
    FROM TASK t
    FOR XML PATH('SomeRoot')

would yield:

<SomeRoot>
  <TaskName>Get Report Parameters</TaskName>
</SomeRoot>
<SomeRoot>
  <TaskName>Loop Report Creation</TaskName>
</SomeRoot>
<SomeRoot>
  <TaskName>Report In Loop</TaskName>
</SomeRoot>
<SomeRoot>
  <TaskName>Get Email Addresses</TaskName>
</SomeRoot>
<SomeRoot>
  <TaskName>Loop Mail Creation</TaskName>
</SomeRoot>
<SomeRoot>
  <TaskName>Send Email In Loop</TaskName>
</SomeRoot>

To get the "SomeRoot" to appear around it, you could do this:

SELECT 
(
    SELECT 
        TaskName
    FROM TASK t
    FOR XML PATH(''),Type
) 
FOR XML PATH('SomeRoot')

If the node name is static (Note the XML PATH(''),Type, which basically ensures that XML path returns XML type data for further processing and doesn't escape it)

If the node name is NOT static, you're stuck with something like this, with the need to convert to and from string to make it work.

SELECT 
    CONVERT(XML,
        '<'+DynamicName+'>' + 
        CONVERT(VARCHAR(MAX),
                (
                    SELECT 
                        TaskName
                    FROM TASK t
                    FOR XML PATH(''),Type
                )
            ) +
            '</'+DynamicName+'>'  
    )
FROM
    (SELECT 'Test' as DynamicName) a

c) Regarding your question about getting different children tags to appear on the same level, it's pretty trivial, and you just have to remember that the usual problem of multiple layers of select ceases to be a problem with xml as an xml select just returns a single xml object. You can then use XML PATH to combine those results in a tree too.

e.g.

SELECT 
    (SELECT top 1 * FROM TASK FOR XML PATH(''),Type),
    (SELECT top 1 * FROM TASK_DETAILS FOR XML PATH(''),Type)

Will return a single row with two columns but if you then apply XML PATH('') To the whole, you've combined them on the same level

SELECT 
    (SELECT top 1 * FROM TASK FOR XML PATH(''),Type),
    (SELECT top 1 * FROM TASK_DETAILS FOR XML PATH(''),Type)
FOR XML PATH('Root')    

d) Column names are converted into nodes for you if by XML PATH. Attributes are quite easy in that you just give the column an alias which is the appropriate xsl path e.g. 'MyNodeName\@MyAttributeName' obviously this precludes attributes that are also dynamically named. For that, in this example, I just built the xml from strings again. This incidentally is why dynamic node names is such a bad idea-- you basically allow your routine to create new attribute names and node names via data in the table... this means that you can't create a decent schema for your routine as you don't know in advance what data might be in the table...

Moving on :)

So, given those building blocks, the easiest thing to do is work from the deepest level, and build it block by block, then combine like above.

I did that for your query, and eventually realised that to make it work hierarchically (i.e. n-nested levels) I had to write is as a function returning XML, which is called passing the parentnode to it (so that the function knows what to filter the result set down to). This will die a horrible death if you hierarchy is ill-formed and circular.

Okay- hopefully there's something in there you can work with. This is purely XML PATH() oriented solution - there are alternative XML methods which can be useful in different situations.