How to retrive two different XML tags using "FROM OPENXML" in SQLServer



I have an XML like this:



<OWNER>
<NAME>Lucas</NAME>
<EMAIL>lucas@sample.com</EMAIL>
<OWNER>
<ITENS>
<MEMBER Nitem="1">
<NAME>Alex</NAME>
<EMAIL>alex@sample.com</EMAIL>
</MEMBER>
<MEMBER Nitem="2">
<NAME>Jane</NAME>
<EMAIL>jane@sample.com</EMAIL>
</MEMBER>
<ITENS>


my actual Stored Procedure already get all the Members correctly using:



INSERT INTO #Temp_XML(id,parentid,nodetype,localname,prefix,namespaceuri,datatype,prev,text)
SELECT id,parentid,nodetype,localname,prefix,namespaceuri,datatype,prev,text
FROM OPENXML(@_IdXML,'//Member',2)


but I also want to insert at this same temporary table #Temp_XML the Owner values (it has the same attributes, name and email). How should I do it?


No comments:

Post a Comment