tag:blogger.com,1999:blog-5837291566877619242.post2946665798238072969..comments2024-01-26T07:25:14.088-05:00Comments on Ask a DBA: How to Load XML Documents Into SQL ServerAsk a DBAhttp://www.blogger.com/profile/15498254989021845763noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-5837291566877619242.post-72039508157459990832012-09-21T19:47:51.841-04:002012-09-21T19:47:51.841-04:00With OPENXML, you can treat the XML files like tab...With OPENXML, you can treat the XML files like tables. So if you have multiple XML files, and there is a common element/attribute in those files that you can join with, then once you bring it into SQL with OPENXML, you can join on that field. For example, say I have 2 product xml files. The first file contains the Name for product 100. The second file contains ProductNumber and ListPrice for product 100. I can join these two files together using the ProductID field to get all the information.<br /><br />Below is an example. Notice that @hdoc2 is the handle to the other xml file.<br /><br />SELECT *<br />FROM OPENXML (@hdoc, '/Subcategories/Subcategory/Products/Product', 2)<br />WITH (<br /> ProductID int, <br /> Name varchar(100)) AS P1<br />INNER JOIN OPENXML (@hdoc2, '/Subcategories/Subcategory/Products/Product', 2)<br />WITH (<br /> ProductID int, <br /> ProductNumber varchar(50), <br /> ListPrice float) AS P2<br />ON P1.ProductID = P2.ProductIDThttps://www.blogger.com/profile/06920602957590196290noreply@blogger.comtag:blogger.com,1999:blog-5837291566877619242.post-21481958474648236842012-09-21T18:40:34.886-04:002012-09-21T18:40:34.886-04:00Please check this:
http://forums.asp.net/t/184397...Please check this:<br /><br />http://forums.asp.net/t/1843973.aspx/1?Consolidating+XML+Files+for+processing+and+uploading+to+Database+<br /><br />I still need to know how to generate result set by joining data of Products stored in multiple XML Files, where each file has data for one Product. I need this badly.<br /><br />Your tutorial was great!<br /><br />Tarek.TarekAHFhttps://www.blogger.com/profile/15914538165615810959noreply@blogger.com