将 XML 导入并粉碎到 SQL 表中 [英] Importing and shredding XML into SQL Table

问题描述

我希望有人可以帮助我，我正在尝试将 XML 元素以 XML 格式导入 SQL 表中格式.

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

首先，我有一个名为 Chassis.xml 的 XML 文件，如下所示.

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>

我正在尝试编写一个 SQL 过程，将元素导入到表格中，这里是我想要的表格布局.

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" />

TypeId 将是一个外键，稍后将定义该 Type 在另一个表中的内容，因此 TypeId 0001 是 Chassis ComponentType.

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.

推荐答案

你是否尝试过类似的事情

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)

这篇关于将 XML 导入并粉碎到 SQL 表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！