parse xml into SQL 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

parse xml into SQL 2005

Author

Topic

brider@syseng.com
Starting Member

2 Posts

Posted - 2008-12-24 : 14:50:45

I have been tasked with importing an xml file into SQL 2005 and creating an export xml file. Here is a sample of the xml:

<ROOT>
<ITEM>
<POST>07.05.01</POST>
<CUSTOMER>1</CUSTOMER>
<ENTRY>
<ZIP>94850</ZIP>
<TYPE>O</TYPE>
<VERSION>
<NO>00043</NO>
<LOT>00043</LOT>
<DETAIL>
<TYPE>P</TYPE>
<CODE>B1</CODE>
</DETAIL>
<DETAIL>
<TYPE>P</TYPE>
<CODE>B2</CODE>
</DETAIL>
</VERSION>
</ENTRY>
<ENTRY>
<ZIP>98000</ZIP>
<TYPE>B</TYPE>
<VERSION>
<NO>00043</NO>
<LOT>00043</LOT>
<DETAIL>
<TYPE>P</TYPE>
<CODE>B2</CODE>
</DETAIL>
<DETAIL>
<TYPE>P</TYPE>
<CODE>B6</CODE>
</DETAIL>
</VERSION>
</ENTRY>
</ITEM>
</ROOT>

I have created a stored procedure to import the file and data. I created another stored procedure to parse the data into respective tables, ITEM, ENTRY, VERSION, and DETAIL. What I'm having trouble with is relating the child data back to the parent. Any suggestions? Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 16:05:51

How to know which is child and parent?