Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 28th, 2011, 12:51 PM
Authorized User
 
Join Date: Feb 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default XML import 1:n relationship

Code:
Hi All,
I have the code below which is for importing xml files
It results in
customernumber accountnumer tp zip --------------- ------------------------------ ---------- ---------- 113 26110328983 31 10217 521 78136363 22 25311
(2 row(s) affected)
order_number remarks --------------- ------------------------------ 42 rush order 219 special package 1320 190 birthday wrap 11 birthday wrap 1181 7 special
(7 row(s) affected)
However what I would like (very much) is the following
nr customernumber accountnumer tp zip ------------------- --------------- ---------- ---------- 1 113 26110328983 31 10217 2 521 78136363 22 25311
nr order_number remarks ------------------- ----------------- 1 42 rush order 1 219 special package 1 1320 1 190 birthday wrap 2 11 birthday wrap 2 1181 2 7 special
so that the order details are connected (through the field 'nr') to the order header.
Any help is very much appreciated.
Kind regards,
John
DECLARE @invoer varchar(4096)
DECLARE @hendel int
SET @invoer = '<root> <customer> <customernumber>113</customernumber> <accountnumer>26110328983</accountnumer> <tp>31</tp> <zip>10217</zip> <orders> <order> <order_number>42</order_number> <remarks>rush order</remarks> </order> <order> <order_number>219</order_number> <remarks>special package</remarks> </order> <order> <order_number>1320</order_number> <remarks></remarks> </order> <order> <order_number>190</order_number> <remarks>birthday wrap</remarks> </order> </orders> </customer> <customer> <customernumber>521</customernumber> <accountnumer>78136363</accountnumer> <tp>22</tp> <zip>25311</zip> <orders> <order> <order_number>11</order_number> <remarks>birthday wrap</remarks> </order> <order> <order_number>1181</order_number> <remarks></remarks> </order> <order> <order_number>7</order_number> <remarks>special</remarks> </order> </orders> </customer> </root>'
EXEC sp_xml_preparedocument @hendel OUTPUT, @invoer
SELECT customernumber, accountnumer, tp, zip FROM OPENXML(@hendel, '/root/customer', 2) WITH (customernumber varchar(15), accountnumer varchar(30), tp varchar(10), zip varchar(10))
SELECT order_number, remarks FROM OPENXML(@hendel, '/root/customer/orders/order', 2) WITH (order_number varchar(15), remarks varchar(30))





Similar Threads
Thread Thread Starter Forum Replies Last Post
HOW TO: Import XML Feed from Another Domain kwilliams SQL Server 2005 1 July 7th, 2009 10:36 AM
Export/Import Utility using XML jujiro_eb SQL Server 2005 0 June 22nd, 2009 06:33 PM
Import XML to SQL table Colonel Angus SQL Server 2000 4 March 23rd, 2007 08:37 AM
XML file w ith parent / child relationship bh0526 VB.NET 3 June 18th, 2003 08:28 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.