Importing and shredding XML into SQL Table

Question

Hi i hope someone can help me, i am trying to import XML elements into an SQL Table, in XML format.

To start with i have an XML file called Chassis.xml that looks like this.

<Chassis>
  <Chassis Id="1" Chassis="blah blah" Suitability="1" Structured="1" />
  <Chassis Id="2" Chassis="blah blah" Suitability="1" Structured="1" />
  <Chassis Id="3" Chassis="Blah Blah" Suitability="1" Structured="1" />
  <Chassis Id="4" Chassis="Blah Blah" Suitability="1" Structured="1" />
</Chassis>

And i am trying to im trying to write an SQL procedure that imports the elements into a table here is the table layout that i wanted.

test.hardwareComponents

Id          TypeId         XmlData
----------------------------------
1            0001         <Chassis Id="1" Chassis="blah blah" Suitability="1" Structured="1" />
2            0001         <Chassis Id="2" Chassis="blah blah" Suitability="1" Structured="1" />

The TypeId will be a foreign key that will define what that Type is in another table later, so TypeId 0001 is a Chassis ComponentType.

Every thing i try keeps on failing i've spent hours and hours trying to do this and i am stumped can anyone help me.

Adriaan Stander · Accepted Answer · 2010-08-12 11:05:11Z

3

Have you tried something like

DECLARE @xml XML

SET @xml = 
'<Chassis> 
  <Chassis Id="1" Chassis="blah blah" Suitability="1" Structured="1" /> 
  <Chassis Id="2" Chassis="blah blah" Suitability="1" Structured="1" /> 
  <Chassis Id="3" Chassis="Blah Blah" Suitability="1" Structured="1" /> 
  <Chassis Id="4" Chassis="Blah Blah" Suitability="1" Structured="1" /> 
</Chassis>'


SELECT  T2.Loc.value('@Id', 'INT') ID,
        T2.Loc.query('.')
FROM    @xml.nodes('/Chassis/Chassis') as T2(Loc)

answered Aug 12, 2010 at 11:05

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthew Over a year ago

Thankyou so much that has solved my problem. Thankyou for answering you have helped me imensly. :)

Collectives™ on Stack Overflow

Importing and shredding XML into SQL Table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related