MSSQL xml数据解析问题

[qq_37400227]

DECLARE @inputStr xml
SET @inputStr = '<Request>
<tag>aaa</tag><tag_c>bbb</tag_c>
<tag>ccc</tag><tag_c>ddd</tag_c>
</Request>'

SELECT @inputStr.value('(/Request/tag)[1]','varchar(100)')
取到aaa
SELECT @inputStr.value('(/Request/tag)[2]','varchar(100)')
取到ccc

下标处能否使用变量.比如我要循环取,并不知道有多少个tag

[OwenZeng_DBA]

引用 楼主 qq_37400227 的回复:

DECLARE @inputStr xml SET @inputStr = '<Request> <tag>aaa</tag><tag_c>bbb</tag_c> <tag>ccc</tag><tag_c>ddd</tag_c> </Request>' SELECT @inputStr.value('(/Request/tag)[1]','varchar(100)') 取到aaa SELECT @inputStr.value('(/Request/tag)[2]','varchar(100)') 取到ccc 下标处能否使用变量.比如我要循环取,并不知道有多少个tag

可以用下面的办法@count就是下标

SELECT @inputStr
DECLARE @count int
SET @count=2;
SELECT @inputStr.query('/Request/tag[position()=sql:variable("@count")]')

用下面的方法可以取的总共有多少个tag

SET @count=1;
SET @totalCount=@data.value('count(/bookstore/book[1]/@*)','INT');
WHILE @count<=@totalCount

[听雨停了]

DECLARE @inputStr xml
SET @inputStr = '<Request>
<tag>aaa</tag><tag_c>bbb</tag_c>
<tag>ccc</tag><tag_c>ddd</tag_c>
</Request>'

DECLARE @idoc int 
EXEC sp_xml_preparedocument @Idoc OUTPUT, @inputStr  
SELECT [TEXT] FROM OPENXML (@Idoc, '/Request/tag',1) WHERE [text] IS NOT NULL 

TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
aaa
ccc

(2 行受影响)

[顺势而为1]

DECLARE @inputStr xml
SET @inputStr = '<Request>
<tag>aaa</tag><tag_c>bbb</tag_c>
<tag>ccc</tag><tag_c>ddd</tag_c>
</Request>'

Select T.C.value('.','varchar(40)')
from @inputStr.nodes('/Request/*') AS T(C)
Where T.C.value('local-name(.)','varchar(20)')='tag'

----------------------------------------
aaa
ccc

(2 row(s) affected)

[kyupis]

SELECT @inputStr.value('count(//Request/tag)','int')

[kyupis]

上面取出tag数量，遍历可用二楼的

SELECT @inputStr
DECLARE @count int
SET @count=2;
SELECT @inputStr.query('/Request/tag[position()=sql:variable("@count")]')

[道素]

DECLARE @inputStr xml
SET @inputStr = '<Request>
<tag>aaa</tag><tag_c>bbb</tag_c>
<tag>ccc</tag><tag_c>ddd</tag_c>
</Request>'
select x.node.value('local-name(.)','varchar(100)') as n ,x.node.value('.','varchar(100)') as v from @inputStr.nodes('Request/*') x(node)

  	n	v
tag          aaa
tag_c      bbb
tag          ccc
tag_c      ddd