如何将xml的嵌套层次结构转换为sql表 [英] How to convert nested hierarchy of xml to sql table

问题描述

使用 MSSQL 2008 和 XQUERY

Using MSSQL 2008 and XQUERY

考虑存储在表中的以下 XML:

Consider the following XML stored in a table:

<ROOT>
  <WrapperElement>
    <ParentElement ID=1>
      <Title>parent1</Title>
      <Description />
      <ChildElement ID="6">
        <Title>Child 4</Title>
        <Description />
        <StartDate>2010-01-25T00:00:00</StartDate>
        <EndDate>2010-01-25T00:00:00</EndDate>
      </ChildElement>
      <ChildElement ID="0">
        <Title>Child1</Title>
        <Description />
        <StartDate>2010-01-25T00:00:00</StartDate>
        <EndDate>2010-01-25T00:00:00</EndDate>
      </ChildElement>
      <ChildElement ID="8">
        <Title>Child6</Title>
        <Description />
        <StartDate>2010-01-25T00:00:00</StartDate>
        <EndDate>2010-01-25T00:00:00</EndDate>
      </ChildElement>
    </ParentElement>
  </WrapperElement>
</Root>

我想把这个xml分解成类似的东西

I want to decompose this xml into something like

PE!ID | PE!Title | PE!Description | CE!ID | CE!Title | CE!StartDate |...
1     | parent1  |                | 6     | child 4  |  2010-... |
1     | parent1  |                | 0     | child1   | 2010-...  |

等

注意:在本例中，每个 ParentElement 可能有许多 ChildElement.我一直在尝试使用 xquery，但是我一直无法浏览复杂的元素.

Note: there may be many ChildElements per ParentElement, in this example. I've been experimenting with xquery however i've not been able to navigate through complex elements as such.

基本上，我试图做与 FOR XML 对表所做的完全相反的事情，只是使用了一组更简单的数据来处理.

Basically, i'm trying to do the exact opposite of what FOR XML does to a table, only with a much more simplistic set of data to work with.

关于下一步去哪里或如何实现这一目标的任何想法?

Any ideas on where to go next or how to accomplish this?

谢谢

推荐答案

这个怎么样(我将 @input 声明为带有你的 XML 内容的 XML 数据类型变量 - 相应地替换):

How about this (I declared @input to be a XML datatype variable with your XML content - replace accordingly):

SELECT
    Parent.Elm.value('(@ID)[1]', 'int') AS 'ID',
    Parent.Elm.value('(Title)[1]', 'varchar(100)') AS 'Title',
    Parent.Elm.value('(Description)[1]', 'varchar(100)') AS 'Description',
    Child.Elm.value('(@ID)[1]', 'int') AS 'ChildID',
    Child.Elm.value('(Title)[1]', 'varchar(100)') AS 'ChildTitle',
    Child.Elm.value('(StartDate)[1]', 'DATETIME') AS 'StartDate',
    Child.Elm.value('(EndDate)[1]', 'DATETIME') AS 'EndDate'
FROM
    @input.nodes('/ROOT/WrapperElement/ParentElement') AS Parent(Elm)
CROSS APPLY
    Parent.Elm.nodes('ChildElement') AS Child(Elm)

您基本上遍历所有 /ROOT/WrapperElement/ParentElemet 节点(作为 Parent(Elm) 伪表)，然后对于这些条目中的每一个，您都执行CROSS APPLY 用于包含在该 ParentElement 中的子元素，并提取出必要的信息.

You basically iterate over all the /ROOT/WrapperElement/ParentElemet nodes (as Parent(Elm) pseudo table), and for each of those entries, you then do a CROSS APPLY for the child elements contained inside that ParentElement and pluck out the necessary information.

应该可行 - 我希望！

Should work - I hope!

这篇关于如何将xml的嵌套层次结构转换为sql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！