Can someone help me to parse the following XML in T-SQL (SQL Server 2005)?
有人可以帮我解析T-SQL中的以下XML(SQL Server 2005)吗?
<Tx>
<T>1</T>
<C>1</C>
<T>2</T>
<C>1</C>
<T>3</T>
<C>1</C>
<T>4</T>
<C>1</C>
</Tx>
I tried the following:
我尝试了以下方法:
SELECT
Tx.query('T').value('.', 'varchar(10)') AS [Column 1],
Tx.query('C').value('.', 'varchar(10)') AS [Column 2]
FROM @MyXml.nodes('Tx') x(Tx)
but it didn't work, as I get the following result:
但它没有用,因为我得到以下结果:
Column 1 Column 2
-------- --------
1234 1111
while expecting (what I want to achieve):
期待(我想要实现的目标):
Column 1 Column 2
-------- --------
1 1
2 1
3 1
4 1
The string is obviously a valid XML, but is it valid for T-SQL?
该字符串显然是一个有效的XML,但它对T-SQL有效吗?
I'll be grateful too if someone can provide a reference that explains how XML works in T-SQL.
如果有人能提供解释XML如何在T-SQL中工作的参考,我将不胜感激。
Thanks in advance.
提前致谢。
3 个解决方案
#1
5
Here is a way to pair the values by position in. First T is matched against first C and so on.
这是一种按位置对值进行配对的方法。首先将T与第一个C匹配,依此类推。
declare @XML xml =
'<Tx>
<T>1</T>
<C>4</C>
<T>2</T>
<C>3</C>
<T>3</T>
<C>2</C>
<T>4</T>
<C>1</C>
</Tx>'
select @XML.value('(/Tx/T[position() = sql:column("N.number")])[1]', 'int') as Column1,
@XML.value('(/Tx/C[position() = sql:column("N.number")])[1]', 'int') as Column2
from master..spt_values as N
where N.type = 'P' and
N.number between 1 and @XML.value('max((count(/Tx/T), count(/Tx/C)))', 'int')
With a different structure of the XML like suggested by marc_s the query is much simpler.
使用marc_s建议的不同XML结构,查询更简单。
declare @XML xml =
'<Tx>
<row>
<T>1</T>
<C>4</C>
</row>
<row>
<T>2</T>
<C>3</C>
</row>
<row>
<T>3</T>
<C>2</C>
</row>
<row>
<T>4</T>
<C>1</C>
</row>
</Tx>'
select T.R.value('T[1]', 'int') as Column1,
T.R.value('C[1]', 'int') as Column2
from @XML.nodes('/Tx/row') as T(R)
#2
3
What are you trying to achieve??
你想要实现什么?
Your current select statement will list out all the <T>
elements below <Tx>
- but only those - not the <C>
nodes, too - and you seem to want to get the values from both kinds of subnodes - right??
您当前的select语句将列出
This will give you all the elements from the <T>
nodes - is that what you're looking for?
这将为您提供
SELECT
Tx.x.value('.', 'varchar(10)') AS [Column 1]
FROM @MyXml.nodes('Tx/T') Tx(x)
This will give you all the elements from inside the <Tx>
node, including their "type" (C or T) - is that what you're looking for?
这将为您提供
SELECT
ColumType = Tx.x.value('local-name(.)', 'varchar(10)'),
ColumnValue = Tx.x.value('.', 'varchar(10)')
FROM @MyXml.nodes('Tx/*') Tx(x)
Update: as resources for learning how to use XQuery in SQL Server, I'd recommend
更新:作为学习如何在SQL Server中使用XQuery的资源,我建议
- SQL Server 2005 XQuery and XML-DML
- Introduction to XQuery in SQL Server 2005
SQL Server 2005 XQuery和XML-DML
SQL Server 2005中的XQuery简介
Your XML is not well suited to do what you're trying to do - there's nothing that "keeps together" the T and C element that obviously belong together - so there's really nothing XQuery can do to parse this the way you want it to.
你的XML并不适合做你想要做的事情 - 没有任何东西可以“整合”明显属于一起的T和C元素 - 所以XQuery根本无法以你想要的方式解析它。
If you had the XML something like this:
如果你有这样的XML:
<Tx>
<Pair>
<T>....</T>
<C>....</C>
</Pair>
<Pair>
<T>....</T>
<C>....</C>
</Pair>
....
</Tx>
then you could get the list of Tx/Pair
nodes and get the T and C elements from that XML fragment. But right now - all you can do is parse out all 8 subnodes of <Tx>
and display their values - that's really all you can do
然后你可以得到Tx / Pair节点列表,并从该XML片段中获取T和C元素。但是现在 - 你所能做的只是解析
#3
3
Use this:
select a.t, b.c
from
(
select t.c.value('.[1]', 'int') [t]
, ROW_NUMBER() OVER(ORDER BY t.c) [rn]
from @MyXml.nodes('Tx/T') t(c)
)a
join
(
select t.c.value('.[1]', 'int') [c]
, ROW_NUMBER() OVER(ORDER BY t.c) [rn]
from @MyXml.nodes('Tx/C') t(c)
)b on b.rn = a.rn
or another way:
或另一种方式:
select t.t [T]
, @MyXml.value('(Tx/C[position() = sql:column("rn")])[1]', 'int') [C]
from
(
select t.c.value('.[1]', 'int') [t]
, ROW_NUMBER() OVER(ORDER BY t.c) [rn]
from @MyXml.nodes('Tx/T') t(c)
)t
#1
5
Here is a way to pair the values by position in. First T is matched against first C and so on.
这是一种按位置对值进行配对的方法。首先将T与第一个C匹配,依此类推。
declare @XML xml =
'<Tx>
<T>1</T>
<C>4</C>
<T>2</T>
<C>3</C>
<T>3</T>
<C>2</C>
<T>4</T>
<C>1</C>
</Tx>'
select @XML.value('(/Tx/T[position() = sql:column("N.number")])[1]', 'int') as Column1,
@XML.value('(/Tx/C[position() = sql:column("N.number")])[1]', 'int') as Column2
from master..spt_values as N
where N.type = 'P' and
N.number between 1 and @XML.value('max((count(/Tx/T), count(/Tx/C)))', 'int')
With a different structure of the XML like suggested by marc_s the query is much simpler.
使用marc_s建议的不同XML结构,查询更简单。
declare @XML xml =
'<Tx>
<row>
<T>1</T>
<C>4</C>
</row>
<row>
<T>2</T>
<C>3</C>
</row>
<row>
<T>3</T>
<C>2</C>
</row>
<row>
<T>4</T>
<C>1</C>
</row>
</Tx>'
select T.R.value('T[1]', 'int') as Column1,
T.R.value('C[1]', 'int') as Column2
from @XML.nodes('/Tx/row') as T(R)
#2
3
What are you trying to achieve??
你想要实现什么?
Your current select statement will list out all the <T>
elements below <Tx>
- but only those - not the <C>
nodes, too - and you seem to want to get the values from both kinds of subnodes - right??
您当前的select语句将列出
This will give you all the elements from the <T>
nodes - is that what you're looking for?
这将为您提供
SELECT
Tx.x.value('.', 'varchar(10)') AS [Column 1]
FROM @MyXml.nodes('Tx/T') Tx(x)
This will give you all the elements from inside the <Tx>
node, including their "type" (C or T) - is that what you're looking for?
这将为您提供
SELECT
ColumType = Tx.x.value('local-name(.)', 'varchar(10)'),
ColumnValue = Tx.x.value('.', 'varchar(10)')
FROM @MyXml.nodes('Tx/*') Tx(x)
Update: as resources for learning how to use XQuery in SQL Server, I'd recommend
更新:作为学习如何在SQL Server中使用XQuery的资源,我建议
- SQL Server 2005 XQuery and XML-DML
- Introduction to XQuery in SQL Server 2005
SQL Server 2005 XQuery和XML-DML
SQL Server 2005中的XQuery简介
Your XML is not well suited to do what you're trying to do - there's nothing that "keeps together" the T and C element that obviously belong together - so there's really nothing XQuery can do to parse this the way you want it to.
你的XML并不适合做你想要做的事情 - 没有任何东西可以“整合”明显属于一起的T和C元素 - 所以XQuery根本无法以你想要的方式解析它。
If you had the XML something like this:
如果你有这样的XML:
<Tx>
<Pair>
<T>....</T>
<C>....</C>
</Pair>
<Pair>
<T>....</T>
<C>....</C>
</Pair>
....
</Tx>
then you could get the list of Tx/Pair
nodes and get the T and C elements from that XML fragment. But right now - all you can do is parse out all 8 subnodes of <Tx>
and display their values - that's really all you can do
然后你可以得到Tx / Pair节点列表,并从该XML片段中获取T和C元素。但是现在 - 你所能做的只是解析
#3
3
Use this:
select a.t, b.c
from
(
select t.c.value('.[1]', 'int') [t]
, ROW_NUMBER() OVER(ORDER BY t.c) [rn]
from @MyXml.nodes('Tx/T') t(c)
)a
join
(
select t.c.value('.[1]', 'int') [c]
, ROW_NUMBER() OVER(ORDER BY t.c) [rn]
from @MyXml.nodes('Tx/C') t(c)
)b on b.rn = a.rn
or another way:
或另一种方式:
select t.t [T]
, @MyXml.value('(Tx/C[position() = sql:column("rn")])[1]', 'int') [C]
from
(
select t.c.value('.[1]', 'int') [t]
, ROW_NUMBER() OVER(ORDER BY t.c) [rn]
from @MyXml.nodes('Tx/T') t(c)
)t