元素层次结构的SQL XML解析查询

Question

我正在尝试编写一个SQL查询，它将接受未定义模式的XML对象(YAY!)并将其转换为ElementName, Value列的两列表。经过一段时间后，我能够得到一个简单的查询(无论如何，我不是一个SQL人)。

DECLARE @strXml XML
SET @strXml = '<xml>
  <FirstName>TEST</FirstName>
  <LastName>PERSON</LastName>
  <DOB>1/1/2000</DOB>
  <TestObject>
    <SomeProperty>CHECKED</SomeProperty>
    <EmbeddedObject>
        <SomeOtherProperty>NOT CHECKED</SomeOtherProperty>
    </EmbeddedObject>
  </TestObject>
</xml>'
DECLARE @XmlMappings TABLE
(        
    NodeName VARCHAR(64),
    Value VARCHAR(128)
) 
INSERT INTO @XmlMappings
SELECT doc.col.value('fn:local-name(.)[1]', 'varchar(64)') AS ElementName,
       doc.col.value('.', 'varchar(128)') AS Value
FROM @strXml.nodes('/xml/*') doc(Col)
SELECT * FROM @XmlMappings

该查询可以处理仅包含第一级元素的指定XML的简单条件。然而，像TestObject和EmbeddedObject这样的元素最终会变得扁平。我想要的是得到某种类型的映射，比如

ElementName                                 | Value
=====================================================
FirstName                                   | TEST
LastName                                    | PERSON
DOB                                         | 1/1/2000
TestObject.SomeProperty                     | CHECKED
TestObject.EmbeddedObject.SomeOtherProperty | NOT CHECKED

对我来说最难的部分是层次结构。操作符。我不在乎它是不是别的分隔符。我不太了解SQL中的XML，甚至不知道要查询什么。

请注意，我也不能使用OPENXML，因为它正在寻找部署在SQL Azure上，而SQL Azure目前不支持该功能。

Answer 1

有CTE和cross apply

;with cte as
(
    select
        convert(varchar(100), x.n.value('fn:local-name(.)','varchar(100)') ) as path,
        convert(varchar(100), x.n.value('fn:local-name(.)','varchar(100)') ) AS name,
        x.n.query('*') AS children,
        x.n.value('.','varchar(1000)') as value
    from @strxml.nodes('/xml/*') AS x(n)
    union all
    select
        convert(varchar(100), x.path + '.' + c.n.value('fn:local-name(.)','varchar(100)') ),
        convert(varchar(100), c.n.value('fn:local-name(.)','varchar(100)') ) ,
        c.n.query('*'),
        c.n.value('.','varchar(1000)')
    from cte x
        cross apply x.children.nodes('*') AS c(n)
)
select path, value from cte where datalength(children) = 5