Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 524
  • Last Modified:

How to get OpenXML to process all nodes within parent node

Hi there
I pass an XML string to my stored procedure with the following format:
<Root>
<Scheme ref='someref''>
<Payment>
<FundName>Fund1</FundName>
<Amount>0</Amount>
</Payment>
<Payment>
<FundName>Fund2</FundName>
<Amount>0</Amount>
</Payment>
.....
</Scheme>
<Scheme ref='anotherref'>
.....
</Scheme>
</Root>

In my stpred procedure I need to load this XML into a table with the following format:

CREATE TABLE #TempSchemes([Scheme] varchar(100) ,
                      [FundName] varchar(30) ,
                      [Amount] varchar(18))

My OpenXML statement look slike this:

EXEC sp_xml_preparedocument @nXmlHandle output, @SchemeData

INSERT INTO #TempSchemeData([Scheme], [FundName], [Amount])
SELECT xScheme, xFundName, xAmount
FROM OPENXML (@nXmlHandle, '/Root/Scheme',1)  
WITH (xScheme varchar(20) './@ref',
xFundName varchar(30) './Payment/FundName',
xAmount varchar(18) './Payment/Amount')

SELECT @nErr = @@Error, @nRowCnt = @@Rowcount
 IF(@nErr <> 0)
 BEGIN
         SELECT @nRetVal = 1  
         EXEC sp_xml_removedocument @nXmlHandle
         GOTO ERR_HANDLER
END              

THis works without error but all I am getting in my table is the FundName and Amount from my very first
<Payment> tag for each scheme when what I want is for it to load every <payment> item and give it the scheme ref of the enclosing <Scheme> tag.

Any ideas much appreciated.

Cheers
Sam
EXEC sp_xml_removedocument @nXmlHandle
        
0
Samm1502
Asked:
Samm1502
1 Solution
 
Snarf0001Commented:
Try this one:

select xScheme, xFundName, xAmount
from openxml(@idoc, 'Root/Scheme/Payment', 1)
with (
      xScheme varchar(20) '../@ref',
      xFundName varchar(30) 'FundName',
      xAmount varchar(18) 'Amount'
)
0
 
Samm1502Author Commented:
Thanks!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now