具有不同子级的SQL XML路径

对,你的样品有很多问题要解决!

首先,我将给出答案,尽管要注意,为了正确地处理层次结构,它必须是递归函数,所以您提供的测试数据必须在永久表中创建,而不是在临时表中创建(更简单) 然后我会指出一些有用的技巧,我用在它来解决这个问题。

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

像这样调用此函数:

SELECT dbo.GetTasks(NULL)

对,我认为值得注意的技巧是:

a)您可以通过简单地从字符串构建XML节点来手动创建XML节点—如果节点名在表中,这将非常有用。唯一需要注意的是,要在一个块周围放置一个open和closing标记,您可能需要首先将块转换为字符串,附加标记,然后将整个块转换为xml(piecemeal不会像convert to xml函数所期望的那样工作已形成的XML。

b)有时必须将东西嵌套在括号中,才能在所有子标记周围实现标记… 举个例子可以更清楚地说明这一点:

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

将屈服:

<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>

要使“someroot”出现在它周围,可以执行以下操作:

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

如果节点名是静态的(请注意xml路径('') Type ,这基本上确保了xml path返回xml类型数据以便进一步处理,并且不会转义它)

如果节点名不是静态的,那么您将陷入这样的问题,需要转换到字符串或从字符串转换为字符串才能使其工作。

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

c)关于让不同的子标记出现在同一级别的问题,这很简单,您只需记住,通常的多层select问题不再是xml的问题,因为xml select只返回一个xml对象。然后也可以使用xml路径将这些结果合并到树中。

例如

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

将返回包含两列的单行,但如果随后将XML路径('')应用于整个行,则将它们组合在同一级别上

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)如果按xml路径,列名将转换为节点。属性非常容易,因为您只需为列提供一个别名,该别名是适当的xsl路径 例如,“mynodename\@myattributename”显然这排除了也动态命名的属性。为此,在本例中,我只是再次从字符串构建XML。顺便说一下,这就是为什么动态节点名是一个糟糕的主意——您基本上允许您的例程通过表中的数据创建新的属性名和节点名……这意味着您不能为您的例程创建一个像样的模式,因为您事先不知道表中可能有哪些数据…

继续前进:

因此,考虑到这些构建块,最简单的事情是从最深层次开始工作,一块一块地构建,然后像上面那样组合。

我为您的查询做了这些,最终意识到要使它按层次结构(即n个嵌套级别)工作,我必须编写一个返回xml的函数,称为将parentnode传递给它(这样函数就知道如何过滤结果集)。如果你的等级制度是不健全的和循环的,这将是一个可怕的死亡。

好吧-希望里面有些东西你可以用。这纯粹是面向xml path()的解决方案-有其他的xml方法,在不同的情况下可能有用。