venkat

How to retrive values from xml file having multiple
rows with multiple levels in sql server 2000 stored
procedure?


example:

C:\folder1\test.xml


---content here
<root>

<File_Info>
<person>
<ID>text1</ID>
<Name>tom</Name>
</person>
<Date type="File Creation">20030331</Date>
<Period>
<Date type="Start">20030301</Date>
<Date type="End">20030331</Date>
</Period>
</File_Info>


--Repeated data start here
--row1 start here


<row>
<Source_Info>
<01>B</O2>
<S1>m</S1>
<S2>s</S2>
</Source_Info>
<person_info>
<ID>56375</ID>
<Email>c@email.com</Email>

<Name>
<P>Mr.</P>
<F>Bernard</F>
<M> </M>

<Last_Name>Flagstone</Last_Name>
<Suffix>III</Suffix>
</Name>
<Address>
<Addrs1>NY</Addrs1>
<Addrs2>Appt 304</Addrs2>
<City>NY</City>
<State>VA</State>
<Zip>2222</Zip>
<Phone type="Home">4900-
78999</Phone>
<Phone type="Work">4900-
78999</Phone>
<Phone type="Fax"> </Phone>
</Address>
<friend>
<Name>

<Prefix>Mrs.</Prefix>

<First_Name>Wendy</First_Name>

<Last_Name>Flagstone</Last_Name>
<Mid_Initial>
</Mid_Initial>
</Name>
<Address>
<Addrs1>NY</Addrs1>
<Addrs2>Appt 304</Addrs2>
<City>NY</City>
<State>VA</State>
<Zip>2222</Zip>
<Phone type="Home">4900-
78999</Phone>
<Phone type="Work">4900-
78999</Phone>
<Phone type="Fax"> </Phone>
</Address>
</friend>
<R1>
<B_ID>56375</B_ID>
<Email>a@xyz.com</Email>
<company>company1</company>
<Name>

<Prefix>Mr.</Prefix>

<First_Name>Paul</First_Name>
<Mid_Initial>D
</Mid_Initial>

<Last_Name>Connor</Last_Name>
<Suffix></Suffix>
</Name>
<Address>
<Addrs1>ct</Addrs1>
<Addrs2>Suite
405</Addrs2>

<City>Fairfax</City>
<State>NY</State>
<Zip>22033</Zip>
<Phone
type="Cell">7899-900</Phone>
<Phone
type="Work">7899-900</Phone>
<Phone type="Fax">
</Phone>
</Address>

</R1>
</person_info>

<person_Visit_Information>
<visit_code>COAS67M</visit_code>
<visit_ID>6543567</visit_ID>
<Visit>Campbell's Terrace</Visit>
<Date type="First Visit">788-
9000</Date>
<Date type="Last Visit">200-
9000</Date>
</person_Visit_Information>
<other_Information>
<Date
type="start_date">20030425</Date>
<Date
type="end_date">20031020</Date>
<Hotel>
<H_ID>645555</H_ID>
<Name>Five Star</Name>

<Street_Number>12</Street_Number>

<room_Number>34</room_Number>
<Price>400</Price>
</Hotel>
</other_Information>


<Registration_Data>
<Questions>
<Question id="Age"
type="Single Choice">
<Answers>

<Answer>20</Answer>
</Answers>
</Question>

<Question id="Residence
city" type="Single
Choice">
<Answers>

<Answer>NY</Answer>
</Answers>
</Question>
<Question id="room type"
type="Single Choice">
<Answers>
<Answer>3
Bedrooms</Answer>
</Answers>
</Question>

<Question id="your choice"
type="Multiple Choice">
<Answers>

<Answer>Books</Answer>

<Answer>Internet</Answer>

<Answer>Magazines</Answer>

<Answer>Referral</Answer>

<Answer>Road signs</Answer>
</Answers>
</Question>

<Question id="Info
Sources" type="Multiple
Choice">
<Answers>

<Answer>Guide Books</Answer>

<Answer>Web Site</Answer>
<Answer>
Mail Flyer</Answer>
</Answers>
</Question>


</Questions>
</Registration_Data>

</row>

--row1 end here
--row2 start here

<row>
<Source_Info>
<01>B2</O2>
<S1>m2</S1>
<S2>s2</S2>
</Source_Info>
<person_info>
<ID>56376</ID>
<Email>ww@email.com</Email>

<Name>
<P>Mr.</P>
<F>gfhgf</F>
<M> </M>
<Last_Name>fgj</Last_Name>
<Suffix>III</Suffix>
</Name>
<Address>
<Addrs1>CT</Addrs1>
<Addrs2>dfhdgh</Addrs2>
<City>CT</City>
<State>VA</State>
<Zip>2222</Zip>
<Phone
type="Home">76867867</Phone>
<Phone
type="Work">67867867</Phone>
<Phone type="Fax"> </Phone>
</Address>
<friend>
<Name>

<Prefix>Mrs.</Prefix>

<First_Name>fhfg</First_Name>

<Last_Name>fghfgh</Last_Name>
<Mid_Initial>fghfg
</Mid_Initial>
</Name>
<Address>
<Addrs1>fhfg</Addrs1>
<Addrs2>fghfg</Addrs2>
<City>fhfg</City>
<State>fghfg</State>
<Zip>fhggfh</Zip>
<Phone
type="Home">5687686</Phone>
<Phone
type="Work">687686</Phone>
<Phone type="Fax"> </Phone>
</Address>
</friend>
<R1>
<B_ID>56375</B_ID>
<Email>a@xyz.com</Email>
<company>company1</company>
<Name>

<Prefix>Mr.</Prefix>

<First_Name>Paul</First_Name>
<Mid_Initial>D
</Mid_Initial>

<Last_Name>Connor</Last_Name>
<Suffix></Suffix>
</Name>
<Address>
<Addrs1>ct</Addrs1>
<Addrs2>Suite
405</Addrs2>

<City>Fairfax</City>
<State>NY</State>
<Zip>22033</Zip>
<Phone
type="Cell">7899-900</Phone>
<Phone
type="Work">7899-900</Phone>
<Phone type="Fax">
</Phone>
</Address>

</R1>
</person_info>

<person_Visit_Information>
<visit_code>COAS67M</visit_code>
<visit_ID>6543567</visit_ID>
<Visit>Campbell's Terrace</Visit>
<Date type="First Visit">788-
9000</Date>
<Date type="Last Visit">200-
9000</Date>
</person_Visit_Information>
<other_Information>
<Date
type="start_date">20030425</Date>
<Date
type="end_date">20031020</Date>
<Hotel>
<H_ID>645555</H_ID>
<Name>Five Star</Name>

<Street_Number>12</Street_Number>

<room_Number>34</room_Number>
<Price>400</Price>
</Hotel>
</other_Information>


<Registration_Data>
<Questions>
<Question id="Age"
type="Single Choice">
<Answers>

<Answer>50</Answer>
</Answers>
</Question>

<Question id="Residence
city" type="Single
Choice">
<Answers>

<Answer>CT</Answer>
</Answers>
</Question>
<Question id="room type"
type="Single Choice">
<Answers>
<Answer>3
Bedrooms</Answer>
</Answers>
</Question>

<Question id="your choice"
type="Multiple Choice">
<Answers>

<Answer>Books</Answer>

<Answer>Internet</Answer>

<Answer>Magazines</Answer>

<Answer>Referral</Answer>

<Answer>Road signs</Answer>
</Answers>
</Question>


<Question id="type"
type="Single Choice">
<Answers>

<Answer>1</Answer>
</Answers>
</Question>
<Question id="send trough"
type="Single Choice">
<Answers>

<Answer>mail</Answer>
</Answers>
</Question>


<Question id="Info
Sources" type="Multiple
Choice">
<Answers>

<Answer>Guide Books</Answer>

<Answer>Web Site</Answer>
<Answer>
Mail Flyer</Answer>
</Answers>
</Question>


</Questions>
</Registration_Data>

</row>
--row1 end here


like any no. of rows.............

</root>

bryant likes

You can either use OpenXML:
http://sqlxml.org/faqs.aspx?faq=39

Or SqlXml BulkLoading:
http://msdn.microsoft.com/library/en-us/sqlxml3/htm/bulkload_7pv0.asp

--
Bryant