如何从XML类型列导入数据并将它们插入另一个表？ [英] How to Import data from XML type column and insert them into another table?

问题描述

大家好，



这是我的场景，我有一个包含XML DATA类型列的表。



案例1：



现在我需要从该xml文件导入数据并需要插入它们位于同一个表的相应列中。



案例2：我想从该xml文件中获取数据，并且必须插入到另一个表中并且可以创建一个这两个表之间的关键关系。



以下是我给出的示例结构。

Hi all,

Here is my scenerio, I have a table which contains a column of type XML DATA type.

Case 1:

Now I need to import the data from that xml file and need to insert them in the respective columns in the same table.

Case 2: I would like to get data from that xml file and have to insert into another table and can make a foregin key relationship between these two tables.

The below is the sample structure I give.

<products>
  <product>
    <sku>1</sku>
    <desc>Book</desc>
<test>pass</test>
  </product>
  <product>
    <sku>2</sku>
    <desc>DVD</desc>
<test>pass</test>
  </product>
  <product>
    <sku>3</sku>
    <desc>Video</desc>
<test>fail</test>
  </product>
</products>

这是现在正在使用的查询，它从路径给出的xml文件所做的是什么...我不希望路径指定..我想要从xml类型的列中获取它。怎么做？

This is the query which am using now, what it does it takes the xml file from the path am giving.. i dont want the path to specify.. i want to take it from the column of xml type. How to do it?

INSERT INTO Products (sku, product_desc) 
SELECT X.product.query('SKU').value('.', 'INT'),
       X.product.query('Desc').value('.', 'VARCHAR(30)')
FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(
     BULK 'D:\Products.xml',
     SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('Products/Product') AS X(product);

我需要提及列名，而不是提供路径。 :)



请提供一些可以成功执行的查询。

在此先感谢..

Instead of giving the path, i need to mention the column name. :)

Please provide me some queries that will execute successfully.
Thanks in advance..

推荐答案

您好，



请尝试使用以下方式：

Hi,

Please try using the following way:

--Creating a temporary table variable

declare @Temp table
(
  XMLCol xml
)

--insert your xml data

insert into @Temp values
('<your xml data>')

--selecting the records from the xml column
select X.N.value(N'(sku)[1]', 'nvarchar(max)') as _sku,
       X.N.value(N'(desc)[1]', 'nvarchar(max)') as _desc,
       X.N.value(N'(test)[1]', 'nvarchar(max)') as _test
from @Temp as T
  cross apply T.XMLCol.nodes(N'/products/product') as X(N)

这篇关于如何从XML类型列导入数据并将它们插入另一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！