如何将 xml 的嵌套层次结构转换为 sql 表
使用 MSSQL 2008 和 XQUERY
考虑存储在表中的以下 XML:
<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 分解为类似
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,但是我无法导航这样的复杂元素。
基本上,我试图做与 FOR XML 对表所做的完全相反的事情,只是使用一组更简单的数据来处理。
关于下一步该去哪里或如何实现这一目标有什么想法吗?
谢谢
Using MSSQL 2008 and XQUERY
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>
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-... |
etc.
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.
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?
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
怎么样(我将
@input
声明为带有您的 XML 内容的 XML 数据类型变量 - 相应地替换):您基本上迭代所有
/ROOT/WrapperElement/ParentElemet
节点(作为Parent(Elm)
伪表),然后对于每个条目,您可以对该ParentElementCROSS APPLY
code> 并提取必要的信息。应该有效 - 我希望!
How about this (I declared
@input
to be a XML datatype variable with your XML content - replace accordingly):You basically iterate over all the
/ROOT/WrapperElement/ParentElemet
nodes (asParent(Elm)
pseudo table), and for each of those entries, you then do aCROSS APPLY
for the child elements contained inside thatParentElement
and pluck out the necessary information.Should work - I hope!