Importing xml file into ms sql server2005

• vsjayashri

  Right there with Babe

  Points: 797

  More actions

  April 15, 2008 at 3:00 am

  #189107

  Hi

  I have an xml file which has nested structure ie parent - child relation from different tables.

  -

  -

  -

  -

  i have to import the order details and customer details into 2 different tables in ms sql server2005.

  order(o_id, customerid) and the cust(customerid, companyname, city).

  Please help

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  April 15, 2008 at 3:14 am

  #802410

  Hi vsjayashri

  Can't see your xml 🙁

  Try replacing every < with &lt; and repost...

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• vsjayashri

  Right there with Babe

  Points: 797

  More actions

  April 15, 2008 at 3:30 am

  #802415

  hi i am reposting the xml structure.

  - <ROOT>

  - <Customers>

  <CustomerID>1111</CustomerID>

  <CompanyName>Sean Chai</CompanyName>

  <City>NY</City>

  <Order OrderID="1" />

  <Order OrderID="2" />

  </Customers>

  - <Customers>

  <CustomerID>1112</CustomerID>

  <CompanyName>Tom Johnston</CompanyName>

  <City>LA</City>

  <Order OrderID="3" />

  </Customers>

  - <Customers>

  <CustomerID>1113</CustomerID>

  <CompanyName>Institute of Art</CompanyName>

  <Order OrderID="4" />

  </Customers>

  </ROOT>

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  April 15, 2008 at 4:05 am

  #802425

  -- Data

  declare @x xml

  set @x = '<ROOT>

  <Customers>

  <CustomerID>1111</CustomerID>

  <CompanyName>Sean Chai</CompanyName>

  <City>NY</City>

  <Order OrderID="1" />

  <Order OrderID="2" />

  </Customers>

  <Customers>

  <CustomerID>1112</CustomerID>

  <CompanyName>Tom Johnston</CompanyName>

  <City>LA</City>

  <Order OrderID="3" />

  </Customers>

  <Customers>

  <CustomerID>1113</CustomerID>

  <CompanyName>Institute of Art</CompanyName>

  <Order OrderID="4" />

  </Customers>

  </ROOT>'

  -- Calculation

  select

  T.c.value('@OrderID', 'int') as o_id,

  T.c.value('../CustomerID[1]', 'int') as customerid

  from

  @x.nodes('//Order') T(c)

  select

  T.c.value('CustomerID[1]', 'int') as customerid,

  T.c.value('CompanyName[1]', 'varchar(30)') as companyname,

  T.c.value('City[1]', 'varchar(30)') as city

  from

  @x.nodes('//Customers') T(c)

  /* Results

  o_id customerid

  ----------- -----------

  1 1111

  2 1111

  3 1112

  4 1113

  customerid companyname city

  ----------- ------------------------------ ------------------------------

  1111 Sean Chai NY

  1112 Tom Johnston LA

  1113 Institute of Art NULL

  */

  I'm sure you can finish off that last bit...

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• vsjayashri

  Right there with Babe

  Points: 797

  More actions

  April 15, 2008 at 4:21 am

  #802432

  thanks for the information