xml - 从 Xml 输入返回行集

Question

我在 Sql Server 2008 中处理 XML 时遇到了困难。我有一个 xml 文档，需要将其解析为行集。我正在尝试提出一个示例查询，当针对 blob 执行该查询时，会返回如下结果:

ID                                   Status
--------                             -------- 
3765aaf7-afaa-4a46-8499-5a61b607692c 0
1234aaf7-afaa-4a46-8499-5a61b607692c 1

这里是示例 XML:

DECLARE @xml XML

SET @xml = '<Batch xmlns="urn:mynamespace">
  <request xmlns:b="urn:mymessages">
    <b:Batches xmlns:c="urn:myentities">
      <c:BatchEntry>
        <c:ID>3765aaf7-afaa-4a46-8499-5a61b607692c</c:ID>
        <c:Status>0</c:Status>
      </c:BatchEntry>
      <c:BatchEntry>
        <c:ID>1234aaf7-afaa-4a46-8499-5a61b607692c</c:ID>
        <c:Status>1</c:Status>
      </c:BatchEntry>
    </b:Batches>
    <b:BatchID>8492cbaa-eea5-479d-86c4-60cb62ac4b7c</b:BatchID>
  </request>
</Batch>'

我最接近的是:

SELECT T.a.query('.') AS ID 
FROM @xml.nodes('/*[local-name() = "Batch"]/*[local-name() = "request"]/*[local-name() = "Batches"]/*[local-name() = "BatchEntry"]/*[local-name() = "ID"]/text()') T(a)

但我无法弄清楚如何基本上选择“Batches”节点，然后为每个“BatchEntry”返回一个行集。我觉得我还不太理解一些 Sql Server xml 运算符..:)

非常感谢您的帮助!

<小时/>

更新 2011-01-07

通过下面的 @IanC 答案，我能够获得一个完整的工作实例，其中包含我需要做的事情的 xml 命名空间。我在这里更新以防万一其他人遇到它。

DECLARE @xml XML

SET @xml = 
'<Batch xmlns="urn:mynamespace">
  <request xmlns:b="urn:mymessages">
    <b:Batches xmlns:c="urn:myentities">
      <c:BatchEntry>
        <c:ID>3765aaf7-afaa-4a46-8499-5a61b607692c</c:ID>
        <c:Status>0</c:Status>
      </c:BatchEntry>
      <c:BatchEntry>
        <c:ID>1234aaf7-afaa-4a46-8499-5a61b607692c</c:ID>
        <c:Status>1</c:Status>
      </c:BatchEntry>
    </b:Batches>
    <b:BatchID>8492cbaa-eea5-479d-86c4-60cb62ac4b7c</b:BatchID>
  </request>
</Batch>'

DECLARE @sp int
DECLARE @hxml int
DECLARE @Result int

DECLARE @t table 
(
    ID UNIQUEIDENTIFIER,
    BatchStatus INT   
)

EXEC    @sp = sp_xml_preparedocument @hxml OUTPUT, @xml
,
'<Batch 
    xmlns:a="urn:mynamespace"
    xmlns:b="urn:mymessages"
    xmlns:c="urn:myentities"
/>'

if @sp != 0 begin
    SET @Result = '0'
    RETURN
end

INSERT INTO @t
SELECT *
FROM OPENXML (@hxml, '/a:Batch/a:request/b:Batches/c:BatchEntry', 2)
WITH
(
    ID          UNIQUEIDENTIFIER    'c:ID',
    BatchStatus INT                 'c:Status'
)

SELECT * FROM @t

EXEC    sp_xml_removedocument @hxml;

Answer 1

尝试将此作为一般模式。如果您需要更多帮助，请告诉我，我将根据您的 XML 进行定制。

DECLARE @t table (
    ProductTypeID   int,
    LowMin          real,
    HiMax           real,
    ParamTypeID     int,
    ParamWeight     real,
    Low             real,
    Hi              real,
    Mode            tinyint         
)

EXEC    @sp = sp_xml_preparedocument @hxml OUTPUT, @XMLText

if @sp != 0 begin
    SET @Result = '0'
    RETURN
end

INSERT INTO @t
SELECT      *
FROM        OPENXML (@hxml, '/query/product/param/item', 2)
WITH        (
            ProductTypeID   int     '../../@type',
            LowMin          real    '../@lowMin',
            HiMax           real    '../@hiMax',
            ParamTypeID     int     '../@type',
            ParamWeight     real    '@weight',
            Low             real    '@low',
            Hi              real    '@hi',
            Mode            tinyint '@mode'
            )

EXEC    sp_xml_removedocument @hxml;