如何将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屋!