如何遍历xml查找值并存储在sql表的单列中

Question

<Goals>
<Goal pct="20">1</Goal>
<Goal pct="20">2</Goal>
<Goal pct="20">3</Goal>
<Goal pct="20">4</Goal>
<Goal pct="20">5</Goal>
</Goals>

我们有一个表，它存储如上所述的XML，我们希望遍历该表，以找到每个节点元素(如1、2、3、4、5(的百分比。

有一个名为GOAL的表，其中存储了1=增长、2=收入、3=中低等值。单列期望值：20%-增长，20%-收入，20%-低-中。

Answer 1

您可以使用CROSS APPLY xml_column.nodes将目标表与XML节点连接起来。一个简单的条件将ID与值匹配：

DECLARE @goalstbl TABLE(id int, name varchar(100));
INSERT INTO @goalstbl VALUES
(1, 'Goal 1'),
(2, 'Goal 2'),
(3, 'Goal 3'),
(4, 'Goal 4'),
(5, 'Goal 5');
DECLARE @xmltbl TABLE(xmlcol XML);
INSERT INTO @xmltbl VALUES
('<Goals>
<Goal pct="20">1</Goal>
<Goal pct="20">2</Goal>
<Goal pct="20">3</Goal>
<Goal pct="20">4</Goal>
<Goal pct="20">5</Goal>
</Goals>');
SELECT goalstbl.*, goalnodes.node.value('@pct', 'int')
FROM @goalstbl goalstbl
CROSS JOIN @xmltbl xmltbl
CROSS APPLY xmltbl.xmlcol.nodes('//Goal') AS goalnodes(node)
WHERE goalstbl.id = goalnodes.node.value('.', 'int')

Answer 2

DECLARE @SYS_ID INT 
SET @SYS_ID = 1234  
SELECT @XML = CONVERT(XML,GOALS) FROM XML_TABLE WHERE SYS_ID = @SYS_ID
SET @RESULT= (SELECT (STUFF((
SELECT ', '+ INVEST_GOAL_DESC +'-'+ cast(T.c.value('@pct','int') as varchar(10)) +'%'
FROM @XML.nodes('Goals/Goal') as T(c) 
CROSS APPLY (SELECT INVEST_GOAL_DESC FROM GOAL WHERE INVEST_GOAL= T.c.value('.','int'))CA
FOR XML PATH('')
), 1, 2, '')
) )