Using the code below I can select from one of the XML_DOCUMENTS that does not have multiple nodes. When there are nodes that require the loop I error due to duplicate. Considering that I can select data from individual nodes based on primary key I feel there must be a way that I can use the xpath to loop over these documents some way. Perhaps not...
Any ideas?
SELECT
extractvalue(XML_DOCUMENT, '/cds-mistint/tt_cds-mistint/store-nbr/text()') as STORE_NBR,
extractvalue(XML_DOCUMENT, '/cds-mistint/tt_cds-mistint/control-nbr/text()') as CONTROL_NBR,
extractvalue(XML_DOCUMENT, '/cds-mistint/tt_cds-mistint/line-nbr/text()') as LINE_NBR,
extractvalue(XML_DOCUMENT, '/cds-mistint/tt_cds-mistint/sales-nbr/text()') as SALES_NBR
FROM TABLE_NAME
where GUID = 'PRIMARY_KEY'