logo标签列表

在T-SQL中解析XML需要帮助

xmlt-sqlxpathxml-parsingxquery
3
有人能帮我在T-SQL（SQL Server 2005）中解析以下XML吗？
<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>

我尝试了以下内容：
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)

但是它没有起到作用，我得到了以下结果：
Column 1    Column 2
--------    --------
1234        1111

期望达到的目标是：

Column 1    Column 2
--------    --------
1           1
2           1
3           1
4           1

这个字符串显然是一个有效的XML，但它对于T-SQL来说是否有效?

如果有人可以提供一个解释XML在T-SQL中如何工作的参考资料，我也会非常感激。

提前感谢。

显然，您想将一个 C 匹配到一个 T。是不是匹配的值之间互相配对很重要，或者只是随意选择任何值都可以？ - Mikael Eriksson
@MikaelEriksson，抱歉，我没明白这个问题。我正在将XML发送到一个存储过程中，然后执行类似于（在将XML插入临时表之后）：基于[Column 2]连接XML“表”和MyTable1，并将[Column 1]插入MyTable2...或者 INSERT INTO MyTable2 (SomeColumn, SomeOtherColumn) SELECT A.[Column 2], B.AnotherColumn FROM @xmlTable A JOIN MyTable1 B ON A.[Column 1] = B.Id。 - TheBlueSky
在您的样本数据中，C 的值始终为 1。一个 XML 中所有 C 的值只会有一个不同的值吗？ - Mikael Eriksson
@MikaelEriksson，如果它总是一个不同的值，那我就没有问题了，对吧？ :) 我给出的示例 XML 只是一个示例，但有点表明 C 与 T 相比具有非常少的不同值。 - TheBlueSky
您是否希望将T的第一个值与C的第一个值配对，将T的第二个值与C的第二个值配对，以此类推......？T的数量可以比C多，也可以比C少。两个或更多连续的T之间是否可能没有C，或者反过来呢？使用数字表格可以在position上执行XQuery。我可以提供这样做的答案，但我想知道配对T和C的规则。顺便说一句，@marc-s是正确的，当他说您应该重新设计您的XML时。 - Mikael Eriksson
显示剩余2条评论
3个回答
5
这里有一种按位置将值配对的方法。首先，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')

如果采用像建议的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)
尝试运行第一个代码时出现以下错误（第二个代码也会出现类似的错误但是不同的行号）：Msg 139，Level 15，State 1，Line 0 无法为本地变量分配默认值。 Msg 137，Level 15，State 2，Line 13 必须声明标量变量“@XML”。 - deutschZuid
@JamesJiao - 这是在 SQL Server 2008 中有效的语法。如果你在使用 SQL Server 2005，你需要将变量 @XML 的声明和赋值分成两个语句，像这样：declare @XML xml; set @XML = 'xml goes here'; - Mikael Eriksson
我选择这个作为我的问题的答案，尽管我不喜欢代码在解析简单内容时可能变得多么丑陋...但如果事情就是这样，那就是这样。我的建议是尽可能像Mikael Eriksson和marc_s建议的那样格式化XML表单。 - TheBlueSky
3
你想实现什么目标？
你当前的选择语句将列出所有<Tx>下方的<T>元素，但只有这些元素，而不是<C>节点，你似乎想要获取两种子节点的值，对吗？
这将给您所有<T>节点中的元素-这是你想要的吗？
SELECT
    Tx.x.value('.', 'varchar(10)') AS [Column 1]
FROM @MyXml.nodes('Tx/T') Tx(x)

这将为您提供<Tx>节点内的所有元素，包括它们的“类型”（C或T） - 这是您要找的吗？
SELECT
    ColumType = Tx.x.value('local-name(.)', 'varchar(10)'),
    ColumnValue = Tx.x.value('.', 'varchar(10)')
FROM @MyXml.nodes('Tx/*') Tx(x)

更新： 作为学习如何在SQL Server中使用XQuery的资源，我建议：

你的XML不适合做你试图完成的事情 - 没有什么东西“把T和C元素紧密地结合在一起”-所以XQuery无法按照你想要的方式解析它。

如果你的XML是这样的：

<Tx>
   <Pair>
      <T>....</T>
      <C>....</C>
   </Pair>
   <Pair>
      <T>....</T>
      <C>....</C>
   </Pair>
   ....
</Tx>

你可以获取 Tx/Pair 节点的列表，并从该 XML 片段中获取 T 和 C 元素。但目前，你只能解析 <Tx> 的所有 8 个子节点并显示它们的值 - 这就是你所能做的。

@TheBlueSky：你不能按照你想要的方式解析它 - 没有“结构”将相应的<T>和<C>元素保持在一起 - 因此，XPath中没有任何可以像你想要的那样解析这些元素作为“column1”和“column2”的方法。 - marc_s
是的，如果我有像你的示例那样的XML，那么这将是一个简单的任务，但不幸的是，情况并非如此。无论如何，感谢您提供的参考资料。 - TheBlueSky
我会为这篇文章投赞成票，因为它部分回答了我的问题。 - TheBlueSky
3
请使用以下内容：
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

另一种方法是：
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
这只是一个解决方法；你的意思是T-SQL无法解析给定的XML吗？ - TheBlueSky
1
@TheBlueSky，据我所知 - 没有。 - Kirill Polishchuk
1
看起来有点可疑的是通过 nodes 实例排序，但显然它使用了 XML 读取器中的 id <ColumnReference Table="[XML Reader with XPath filter]" Column="id" />。Adam Machanic 在 Uniquely Identifying XML Nodes with DENSE_RANK 中进行了博客发布。+1 - Mikael Eriksson
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接