Geek Answers Handbook

Import and chop XML into a SQL table

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

To start, I have an XML Chassis.xml file 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'm trying to try to write an SQL procedure that imports the elements into a table, here is the table 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" />

TypeId will be a foreign key that will determine that this type is in another table later, so TypeId 0001 is a ComponentType component for the chassis.

Every thing that I try to continue fails, I spent hours and hours trying to do this, and I'm at a dead end, can someone help me.

+3
source share
1 answer

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)
+3
source

More articles:


All Articles