how to user OPENXML and relate information of two different table data..... [modified]

[Sasmi_Office]

Dear Friends,

<List>
<Entry>
<id>1</uid>
<lastName>abc</lastName>
<idList>
<id>
<id>1</uid>
<idType>pqr</idType>
<idNumber>1001</idNumber>
<idCountry>INDIA</idCountry>
</id>
<id>
<id>2</uid>
<idType>xyz</idType>
<idNumber>1002</idNumber>
<idCountry>USA</idCountry>
</id>
</idList>
</Entry>

Above is the xml i have in my database i want to read this information and return output as given below id LastName id idType idNumber idCountry 1 abc 1 pqr 1001 INDIA 1 abc 2 xyz 2001 USA i am using OPENXML in my stored procedure the code is as given below

		DECLARE @HANDLE INT 
		DECLARE @XMLDOC XML
		
		SET @XMLDOC = (select ofacList from tbl\_OFAC\_SDN\_List)

	        EXEC SP\_XML\_PREPAREDOCUMENT  @HANDLE OUTPUT,@XMLDOC
			
		SELECT \* FROM OPENXML(@HANDLE, '/List/Entry', 2)	
		WITH (\[id\] int, lastName nvarchar(100)) 

                    EXEC sp\_xml\_removedocument @HANDLE

till this all working fine but i don't know how to relate the id information with the main data and how to get the output as well as if i want to add this information into two table (tblmainTable and idDetailsTable) how i will manage it's relation. tblmainTable Record id LastName 1 abc idDetailsTable Records uid id idType idNumber idCountry 1 1 pqr 1001 INDIA 1 2 xyz 2001 USA please help me to come out of this. thanks and regard's in advance

Sasmi

modified on Tuesday, March 15, 2011 3:35 AM