如何在sql server 2008中读取xml标记子值 [英] how to read xml tag child values in sql server 2008

问题描述

我在xml字符串下面有

HI i have below xml string

<DefaultState>
       <state>FL</state>
       <State>CO</State>
   </DefaultState>
   <ID>kkkkk</ID>

在sql server中使用以下查询iam能够获取id中的值，但如何在sql server中获取默认状态的多个值，请对此提供帮助

using below query in sql server iam able to get the value in id but how can i get the multiple values of the default state in sql server please help in this regards

@ID=DTO.rows.value('ID)[1]','varchar(50)'),
FROM @ProjectXml.nodes('/Gain') DTO(rows)

<pre lang="HTML">

推荐答案

尝试一下.为我工作

Try this. Worked for me

DECLARE @xmlVal as XML


SET @xmlVal ='<gain><defaultstate>
       <state>FL</state>
       <state>CO</state>
   </defaultstate>
   <id>kkkkk</id></gain>'


SELECT
Gain.DefaultState.value('../ID[1]','nvarchar(50)') Id,
Gain.DefaultState.value('state[1]','nvarchar(50)') State1,
Gain.DefaultState.value('State[1]','nvarchar(50)') State2
FROM
 @xmlVal.nodes('Gain/DefaultState') as Gain(DefaultState)

希望这会有所帮助，如果是，那么请接受并投票答复.任何对此的任何疑问/问题都将受到欢迎.

谢谢&问候
RDBurmon.Sr.软件工程师

Hope this helps , If yes then plz accept and vote the answer. Any queries / questions on this are always welcome.

Thanks & Regards
RDBurmon.Sr.Software Engineer

这篇关于如何在sql server 2008中读取xml标记子值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！