在SQL Server中将XML标记存储在表中以获取不同的XML大小 [英] Store XML Tags in Table for Different XML Size in SQL Server

问题描述

我在SQL Server中有代码，该代码从XML提取标记并将它们存储在临时表（#result）中。现在，假设所有XML的结构都相同，则游标针对每个XML循环运行。下面的示例：

I have code in SQL Server which extracts the tags from XML and stores them in a temp table (#result). Right now the cursor runs in a loop for each XML assuming that structure of all XMLs is same. Example below:

XML1 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3></Root>

结果表：

result Table:

Tag1    |   Tag2    |   Tag3
--------|-----------|--------
val1    |   val2    |   val3

但是现在我们有一些XML的标签数量可能更少。下面的示例：

But now we have some XMLs which can have less number of tags. Example below:

XML1 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3></Root>
XML2 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3><Tag4>val4</Tag4></Root>
XML3 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2></Root>

结果表：

result table:

Tag1    |   Tag2    |   Tag3    |   Tag4
--------|-----------|-----------|--------
val1    |   val2    |   val3    |       
--------|-----------|-----------|--------
val1    |   val2    |   val3    |   val4    
--------|-----------|-----------|--------
val1    |   val2    |           |       

下面是我现有的光标代码。

Below is my existing cursor code.

Open C_XML
Fetch next from C_XML into @input_xml
    while @@FETCH_STATUS = 0
    Begin
     Create Table #MyTempTable (
       name varchar(max),
       value varchar(max)
    );
    insert into #MyTempTable
    SELECT
    bar.value('local-name(.)','VARCHAR(max)') as name,
    bar.value('./.','VARCHAR(max)')  as value
    FROM
    @input_xml.nodes('/Root/*') AS input_xml(bar)
    DECLARE @name NVARCHAR(MAX) = ''
    DECLARE @val NVARCHAR(MAX) = ''
    Declare @Query NVARCHAR(MAX) = ''
    SELECT  @name +=   QUOTENAME(name)+ ','
    FROM
    (
        SELECT name
        FROM #MyTempTable
    ) AS ColName
    -- remvoing last comma
    SET @name = LEFT(@name, LEN(@name)-1)
    SET @Query =
    'SELECT * INTO ##temp2 FROM
    (SELECT
        name,
        value
    FROM
        #MyTempTable
    )
    AS TempTable
    PIVOT(
        max(value)
        FOR name IN (' + @name +')
    ) AS SPivotTable'

    EXECUTE sp_executesql @Query
    drop table #MyTempTable
    select * from ##temp2
    if (@count = 1 )
    begin
        select * into #result from ##temp2
    end
    else 
    begin
        insert into #result     
            select * from ##temp2
    end
    drop table ##temp2

    Set @count = @count + 1

Fetch next from C_XML  into @input_xml
END   
CLOSE C_XML;  
DEALLOCATE C_XML;

请建议我如何增强此代码，使其对于上述所有类型的XML都是动态的例。当前它给出的错误是：

Please suggest how I can enhance this code to make it dynamic for all type of XMLs as mentioned in above example. Currently its giving error:

#result 表定义不正确。

推荐答案

如果我正确理解了这一点，则必须首先分析每种XML以找到任何XML。现有元素名称。使用此名称列表，您必须通用地构建一个SELECT，覆盖表中任何位置的任何元素名称...

If I get this correctly, you would have first to analyse each and any XML to find any existing element name. With this list of names you have to build a SELECT generically, covering any element name anywhere in your table...

我认为这是错误的方法...

I think this is the wrong approach...

此外，在极少数情况下，实际上需要使用游标。在几乎任何情况下，如果您认为需要游标，都应​​该重新考虑您的方法。

Furthermore, there are very few cases, where a CURSOR is actually needed. In almost any case, if you think you'd need a cursor, you should rethink your approach.

我建议您首先创建一个经典的EAV列表作为登台表：

I'd suggest first to create a classical EAV-list as a staging table:

-一个样机模拟您的问题

DECLARE @YourTable TABLE(ID INT IDENTITY, YourXml XML);
INSERT INTO @YourTable VALUES ('<Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3></Root>')
,('<Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3><Tag4>val4</Tag4></Root>')
,('<Root><Tag1>val1</Tag1><Tag2>val2</Tag2></Root>');

-一种基于集合的方法，无需游标即可获取每个值

--a set-based approach to get every value without the need of a cursor

SELECT t.ID
      ,A.AnyNode.value('local-name(.)','nvarchar(max)') AS ElementName
      ,A.AnyNode.value('text()[1]','nvarchar(max)') AS ElementValue
FROM @YourTable t
CROSS APPLY t.YourXml.nodes('/Root/*') A(AnyNode);

使用此结果，您可以继续使用标准SQL。如果需要，可以从此处创建动态SQL ...

With this result you can proceed with standard SQL. If needed, you can create your dynamic SQL from here...

这篇关于在SQL Server中将XML标记存储在表中以获取不同的XML大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！