Hi,

This is my first post here. I tried searching the forum to see if any similar questions were answered earlier. I am having SQL Server 2005 development background. I am trying look for its equivalent functionality in DB2.

I have stored procedure input coming as xml host variable. I want to query the xml variable to get the result in tabular format and then use it further for my sql processing. In T-SQL, this is how I used do it.

Code:
DECLARE @XML_STR XML = 
'<SchemaList>
<Record FileNo=1 FileRecNo=1 RecName="cpA"></Record>
<Record FileNo=1 FileRecNo=2 RecName="cpAB"></Record>
<Record FileNo=2 FileRecNo=1 RecName="cpXA"></Record>
<Record FileNo=2 FileRecNo=2 RecName="cpYA"></Record>
<Record FileNo=2 FileRecNo=3 RecName="cpA"></Record>
</SchemaList>'

UPDATE T
SET REC_TEXT = S.REC_TEXT
FROM myschema.mytable 
INNER JOIN
(SELECT record.value('@FileNo','INTEGER') AS [FILE_NO]
           ,record.value('@FileRecNo','INTEGER') AS [FILE_REC_NO]
           ,record.value('@RecName','VARCHAR(MAX)') AS [REC_TEXT]
   FROM @XML_STR.nodes('SchemaList/Record') node(record)
) S
 ON T.FILE_NO = S.FILE_NO
AND T.FILE_REC_NO = S.FILE_REC_NO
AND T.REC_ID = 10
I am trying to figure out how can I achieve equivalent in DB2. We will be using DB2 UDB v8.2 on Z/OS. Whatever samples that I could find was to create XML output or inserting data into xml columns. But I did not see anything which parses xml host variable.

First question - is this possible with v8? If yes, then what equivalent functions needs to be used? If not, what are my alternative options. Please let me know.

Thanks
kip