如何在SQL Server中将XML文件读入表格格式

I have gone through different search and found many similar posts in which the solution was given to transform XML into Tabular format. Below is the sample data of a single row's column I'm attaching and also the basic query which I have done so far.

我进行了不同的搜索，发现了许多类似的文章，其中给出了将XML转换为表格格式的解决方案。下面是我所附加的单个行的列的示例数据，以及我到目前为止所做的基本查询。

<DS_systeminfo>
<Systeminfo>
  <Property>CurrentLanguage</Property>
  <Property_value>en-US</Property_value>
</Systeminfo>
<Systeminfo>
  <Property>Manufacturer</Property>
  <Property_value>LENOVO</Property_value>
</Systeminfo>
<Systeminfo>
  <Property>SerialNumber</Property>
  <Property_value>789654</Property_value>
</Systeminfo>
<Systeminfo>
  <Property>Caption</Property>
  <Property_value>ATTT</Property_value>
</Systeminfo>   
  <Property>Manufacturer</Property>
  <Property_value>LENOVO</Property_value>
</Systeminfo>  
<Systeminfo>
  <Property>WindowsDirectory</Property>
  <Property_value>C:\WINDOWS</Property_value>
</Systeminfo>

and the query is below:

查询如下:

SELECT SerialNumber, 
Cast(SystemInfoXML AS XML).value('(/DS_systeminfo/Systeminfo/Property)[1]', 'varchar(100)') AS Caption,
Cast(SystemInfoXML AS XML).value('(/DS_systeminfo/Systeminfo/Property_value)[1]', 'varchar(100)') AS Value
FROM TerminalsDetail

This is fetching only first node's value , I want to select all the nodes dynamically in a single query , may be using cursor .

这只是获取第一个节点的值，我想动态地选择一个查询中的所有节点，可能是使用游标。

the data given is of single row, I have more over 100 rows for which I need to convert to tabular format.

给定的数据是单行的，我有超过100行需要转换为表格格式。

any kind suggestion will be helpful.

任何善意的建议都会有帮助。

2 个解决方案

#1

Declare @YourTable table (ID int,SystemInfoXML xml)
Insert Into @YourTable values
(1,'<DS_systeminfo><Systeminfo><Property>CurrentLanguage</Property><Property_value>en-US</Property_value></Systeminfo><Systeminfo><Property>Manufacturer</Property><Property_value>LENOVO</Property_value></Systeminfo><Systeminfo><Property>SerialNumber</Property><Property_value>789654</Property_value></Systeminfo><Systeminfo><Property>Caption</Property><Property_value>ATTT</Property_value></Systeminfo><Systeminfo><Property>Manufacturer</Property><Property_value>LENOVO</Property_value></Systeminfo><Systeminfo><Property>WindowsDirectory</Property><Property_value>C:\WINDOWS</Property_value></Systeminfo></DS_systeminfo>')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select [Caption] = f.n.value('(Property)[1]','varchar(50)') 
                      ,[Value]   = f.n.value('(Property_value)[1]','varchar(50)') 
                 From  A.SystemInfoXML.nodes('DS_systeminfo') t(n)
                 Cross Apply t.n.nodes('Systeminfo') f(n)
             ) B

Returns

返回

ID  Caption           Value
1   CurrentLanguage   en-US
1   Manufacturer      LENOVO
1   SerialNumber      789654
1   Caption           ATTT
1   Manufacturer      LENOVO
1   WindowsDirectory  C:\WINDOWS

#2

Something like this:

是这样的:

WITH the_data AS (
  SELECT CAST(SystemInfoXML AS XML) AS XML_DATA
  FROM TerminalsDetail
)
SELECT 
  XML_DATA.query('/DS_systeminfo/Systeminfo/Property[1]') as Caption,
  XML_DATA.query('/DS_systeminfo/Systeminfo/Property_value[1]') as Value
FROM cte

Hat Tip

帽子提示

• https://*.com/a/26394172/215752
• https://*.com/a/26394172/215752