使用TSQL，XQuery结果返回> 1 我需要在查询中搜索的 ID

发布于 2024-09-24 22:17:56 字数 1074 浏览 4 评论 0原文

我在 SQL Server 2005 DB 中有一个列，其中包含存储为字符串的 XML。以下元素树

<DriverDetails>
  <DriverDetail>
    <ID>2334</ID>
    <PRN>1</PRN>
  </DriverDetail>
  <DriverDetail>
    <ID>2335</ID>
    <PRN>2</PRN>
  </DriverDetail>
  <DriverDetail>
    <ID>2336</ID>
    <PRN>3</PRN>
  </DriverDetail>
  <DriverDetail>
    <ID>2337</ID>
    <PRN>4</PRN>
  </DriverDetail>
</DriverDetails>

在该 XML 中，我使用

CONVERT(xml, detailRiskInformation).query('
            //DriverDetails
        ')

检索该元素：我需要查询每个 DriverDetail 节点中的每个 ID，以查看它是否存在于另一个名为 DriverDetails 的表中。相关列是[DriverDetail].[Id]。

现在，我可以像这样运行 XQuery：

CONVERT(xml, detailRiskInformation).query('
            for $i in //DriverDetail
            return data( $i )
        ')

但是它只返回一个结果，其中 4 个 ID 用空格分隔。

如何在一个查询中对每个 ID 执行迭代查询？或者，如果没有，我如何使用光标或更聪明的东西来获取这些内容？

提前致谢蚂蚁

原文

I have a column in an SQL Server 2005 DB which contains an XML stored as a string. Within that XML is the following element tree

<DriverDetails>
  <DriverDetail>
    <ID>2334</ID>
    <PRN>1</PRN>
  </DriverDetail>
  <DriverDetail>
    <ID>2335</ID>
    <PRN>2</PRN>
  </DriverDetail>
  <DriverDetail>
    <ID>2336</ID>
    <PRN>3</PRN>
  </DriverDetail>
  <DriverDetail>
    <ID>2337</ID>
    <PRN>4</PRN>
  </DriverDetail>
</DriverDetails>

I've retrieved this using:

CONVERT(xml, detailRiskInformation).query('
            //DriverDetails
        ')

I need to query each ID within each DriverDetail node to see it if exists in another table, called DriverDetails. The relevant column is [DriverDetail].[Id].

Now, I can run XQuery like this:

CONVERT(xml, detailRiskInformation).query('
            for $i in //DriverDetail
            return data( $i )
        ')

However it just returns a single result with the 4 IDs separated by spaces.

How can I perform an iterative query on each of these IDs in one query? Or, if not, how can I get these out using a cursor or something much cleverer?

Thanks in advance
Ant

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月隐月明月朦胧 2024-10-01 22:17:56

如果您希望检索 ID 值作为“表”以在选择中使用，请尝试类似的操作

DECLARE @xml XML

SET @xml = '
<DriverDetails> 
  <DriverDetail> 
    <ID>2334</ID> 
    <PRN>1</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2335</ID> 
    <PRN>2</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2336</ID> 
    <PRN>3</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2337</ID> 
    <PRN>4</PRN> 
  </DriverDetail> 
</DriverDetails>
'

SELECT  T.c.value('.', 'int') ID
FROM    @xml.nodes('/DriverDetails/DriverDetail/ID') T(c)

或者从表列中检索类似的内容

DECLARE @Table TABLE(
        XmlVal XML
)

INSERT INTO @Table
SELECT '<DriverDetails> 
  <DriverDetail> 
    <ID>2334</ID> 
    <PRN>1</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2335</ID> 
    <PRN>2</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2336</ID> 
    <PRN>3</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2337</ID> 
    <PRN>4</PRN> 
  </DriverDetail> 
</DriverDetails>
'

INSERT INTO @Table
SELECT '<DriverDetails> 
  <DriverDetail> 
    <ID>1</ID> 
    <PRN>1</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2</ID> 
    <PRN>2</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>3</ID> 
    <PRN>3</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>4</ID> 
    <PRN>4</PRN> 
  </DriverDetail> 
</DriverDetails>
'


SELECT  T2.Loc.value('.', 'int') ID
FROM    @Table T
CROSS APPLY XmlVal.nodes('/DriverDetails/DriverDetail/ID') as T2(Loc)

If you wish to retrieve the ID values as a 'TABLE' to use in a select, try something like this

DECLARE @xml XML

SET @xml = '
<DriverDetails> 
  <DriverDetail> 
    <ID>2334</ID> 
    <PRN>1</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2335</ID> 
    <PRN>2</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2336</ID> 
    <PRN>3</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2337</ID> 
    <PRN>4</PRN> 
  </DriverDetail> 
</DriverDetails>
'

SELECT  T.c.value('.', 'int') ID
FROM    @xml.nodes('/DriverDetails/DriverDetail/ID') T(c)

Or from a table column it would be something like

DECLARE @Table TABLE(
        XmlVal XML
)

INSERT INTO @Table
SELECT '<DriverDetails> 
  <DriverDetail> 
    <ID>2334</ID> 
    <PRN>1</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2335</ID> 
    <PRN>2</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2336</ID> 
    <PRN>3</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2337</ID> 
    <PRN>4</PRN> 
  </DriverDetail> 
</DriverDetails>
'

INSERT INTO @Table
SELECT '<DriverDetails> 
  <DriverDetail> 
    <ID>1</ID> 
    <PRN>1</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>2</ID> 
    <PRN>2</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>3</ID> 
    <PRN>3</PRN> 
  </DriverDetail> 
  <DriverDetail> 
    <ID>4</ID> 
    <PRN>4</PRN> 
  </DriverDetail> 
</DriverDetails>
'


SELECT  T2.Loc.value('.', 'int') ID
FROM    @Table T
CROSS APPLY XmlVal.nodes('/DriverDetails/DriverDetail/ID') as T2(Loc)

回复收藏 0 原文

~没有更多了~