SQL Server中的查询XML字段

Question

我使用的是sql server2008 R2。我有一个包含列XXML的表X，其结构如下

<rec>
  <set>
    <Raw CLOrderID="GGM-30-08/24/10" Rej="Preopen" Sym="A" Tm="06:36:29.524" />
  </set>
</rec>

我想解析上面的列XXML并返回如下输出：

CLOrderID                Rej       Sym        Tm
GGM-30-08/24/10          Preopen   A           06:36:29.524

Answer 1

这可以通过几个for xml调用来完成。如果存在/rec/set/Raw/@*，这种结构对于将来的模式更改也是灵活的。如果这种情况发生了变化，您可以随时为您想要获取的属性添加一个具有新路径的管道。希望这能有所帮助。

declare @x table (id int identity(1,1) primary key, x xml)
insert into @x (x) values ('<rec>
  <set>
    <Raw CLOrderID="GGM-30-08/24/10" Rej="Preopen" Sym="A" Tm="06:36:29.524" />
  </set>
</rec>')
select a.id, cast((
    select (
        select x.attribs.value('local-name(.)','nvarchar(20)') + ' '
        from @x t
            outer apply t.x.nodes('/rec/set/Raw/@*') x(attribs)
        where t.id = a.id
        for xml path('')
        ), (
        select x.attribs.value('.','nvarchar(20)') + ' '
        from @x t
            outer apply t.x.nodes('/rec/set/Raw/@*') x(attribs)
        where t.id = a.id
        for xml path('')
        )
    for xml path('')
) as varchar(500))
from @x a

Answer 2

使用nodes()分解XML，使用value()提取属性值。

select T.X.value('@CLOrderID', 'nvarchar(100)') as CLOrderID,
       T.X.value('@Rej', 'nvarchar(100)') as Rej,
       T.X.value('@Sym', 'nvarchar(100)') as Sym,
       T.X.value('@Tm', 'time(3)') as Tm
from dbo.X
  cross apply X.XXML.nodes('/rec/set/Raw') as T(X)

如果您确信每个XML只提取一行，那么您可以直接获得属性值，而无需首先分解。

select X.XXML.value('(/rec/set/Raw/@CLOrderID)[1]', 'nvarchar(100)') as CLOrderID,
       X.XXML.value('(/rec/set/Raw/@Rej)[1]', 'nvarchar(100)') as Rej,
       X.XXML.value('(/rec/set/Raw/@Sym)[1]', 'nvarchar(100)') as Sym,
       X.XXML.value('(/rec/set/Raw/@Tm)[1]', 'time(3)') as Tm
from dbo.X