Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 28th, 2011, 11:51 AM
Authorized User
Points: 94, Level: 1
Points: 94, Level: 1 Points: 94, Level: 1 Points: 94, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: , , .
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))
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 10:10 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.