TSQL 读取基于 Element 属性的 XML 文件

Question

我需要帮助从我收到的XML文件中提取数据（并且无法控制），通常不是任何问题，但是这个文件的结构方式让我感到困惑。 下面是示例 XML。我搜索过的所有文章都没有解决方案，似乎可以以我需要的方式处理此XML文件

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
<Entities TotalResults="3">
<Entity Type="testing"><Fields>
<Field Name="type"><Value>Internal</Value></Field>
<Field Name="name"><Value>Customer123</Value></Field>
<Field Name="updated"><Value>Yes</Value></Field>
</Fields></Entity>  
<Entity Type="testing"><Fields>
<Field Name="type"><Value>External</Value></Field>
<Field Name="name"><Value>CustomerABC</Value></Field>
<Field Name="updated"><Value>Yes</Value></Field>
</Fields></Entity>
<Entity Type="testing"><Fields>
<Field Name="type"><Value>Unknown</Value></Field>
<Field Name="name"><Value>Customer456</Value></Field>
<Field Name="updated"><Value>No</Value></Field>
</Fields></Entity>
</Entities>

我想要的是

实体

实体类型名称已更新

1 内部客户123 是

2 外部客户ABC 是

3 未知客户456 否

我可以从文件中获取数据，但不能以我需要的格式获取数据。

DECLARE @input XML
SELECT @input = CAST(x AS XML)FROM OPENROWSET(BULK 'C:Test.xml', SINGLE_BLOB) AS T(x)

 SELECT T1.steps.value('Value[1]', 'nvarchar(Max)') As EntityType
   FROM @input.nodes('Entities/Entity/Fields/Field') AS Tbl(Scan)
   CROSS APPLY Tbl.Scan.nodes('.') as T1(steps)       
   Where  Tbl.Scan.value('(@Name)[1]', 'varchar(max)') = 'type'

   SELECT 
   T1.steps.value('Value[1]', 'nvarchar(Max)') As Name
   FROM @input.nodes('Entities/Entity/Fields/Field') AS Tbl(Scan)
   CROSS APPLY Tbl.Scan.nodes('.') as T1(steps)       
   Where  Tbl.Scan.value('(@Name)[1]', 'varchar(max)') = 'name'
   SELECT 
   T1.steps.value('Value[1]', 'nvarchar(Max)') As Updated
   FROM @input.nodes('Entities/Entity/Fields/Field') AS Tbl(Scan)
   CROSS APPLY Tbl.Scan.nodes('.') as T1(steps)       
   Where  Tbl.Scan.value('(@Name)[1]', 'varchar(max)') = 'updated'

这给了我三个单独的表，每个表有 3 行，是否可以运行一个查询来根据需要返回数据？

Answer 1

以防有人感兴趣....我们做了以下...

DECLARE @input XML
DECLARE @Total int
DECLARE @Count int
DECLARE @workXMl XML
DECLARE @Type varchar(max)
DECLARE @Name varchar(max)
DECLARE @Updated varchar(max)
SELECT @input = CAST(x AS XML)
FROM OPENROWSET(BULK 'C:TEST.xml', SINGLE_BLOB) AS T(x)
IF OBJECT_ID('tempdb..#XMLData')   IS NOT NULL DROP TABLE #XMLData
IF OBJECT_ID('tempdb..#1')   IS NOT NULL DROP TABLE #1
CREATE TABLE #XMLData(
    [Type] [varchar](max) Null,
    [Name] [varchar](max) Null,
    [Updated] [varchar](max) Null)
SELECT T2.Loc.query('.') as Result into #1
FROM  @input.nodes('Entities/Entity/Fields') as T2(Loc) 
DECLARE my_cursor 
CURSOR FAST_FORWARD FOR 
Select Result from #1
OPEN my_cursor  
FETCH NEXT FROM my_cursor INTO @workXML
WHILE @@FETCH_STATUS = 0 
BEGIN  
SET @Updated = (SELECT @workXML.value('(Fields/Field[@Name="updated"])[1]', 'nvarchar(max)'))
    SET @Name = (SELECT @workXML.value('(Fields/Field[@Name="name"])[1]', 'nvarchar(max)'))
    SET @Type = (SELECT @workXML.value('(Fields/Field[@Name="type"])[1]', 'nvarchar(max)'))
    INSERT INTO #XMLData
    Values (@Type,@Name,@Updated )
FETCH NEXT FROM my_cursor INTO @workXML
END 
CLOSE my_cursor;
DEALLOCATE my_cursor;
Select * FROM #XMLData