CODEWITHSUNDEEP
sqlsql-serverxml
Gooks
Gooks

Reputation: 53

how to traverse xml for finding values and store in single column of sql table

  <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>

We have a table which stores XML like above and through which we want to traverse to find percentages for each node element like 1, 2, 3, 4, 5.

There is a table called GOAL which has Values stores for 1 = Growth , 2 = Income , 3 = Low Medium etc. Expected Value in single Column : 20 % - Growth , 20% - Income , 20% - Low - Medium.

Upvotes: 1

Views: 120

Answers (2)

Gooks
Gooks

Reputation: 53

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, '')
        ) )

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

You could use CROSS APPLY xml_column.nodes to join the goals table with XML nodes. A simple where condition will match ids with values:

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')

Upvotes: 2

Related Questions