没有记录元素的XML需要使用TSQL / Server转换为表

Question

没有记录元素的XML需要在Server中使用T转换为表。

DECLARE @X XML = '<ROOT>
                      <RECORD>
                          <ID>1</ID>
                          <AppCode>Code 1</AppCode>
                          <ID>2</ID>
                          <AppCode>Code 2</AppCode>
                          <ID>3</ID>
                          <AppCode>Code 3</AppCode>
                          <ID>4</ID>
                          <AppCode>Code 4</AppCode>
                          <ID>5</ID>
                          <AppCode>Code 5</AppCode>
                      </RECORD>
                  </ROOT>'

我试过像这样

SELECT
    CASE WHEN items.item.value('local-name(.)', 'varchar(300)') = 'ID' 
            THEN items.item.value('text()[1]', 'varchar(300)') 
    END AS ID,
    CASE WHEN items.item.value('local-name(.)', 'varchar(300)') = 'AppCode' 
            THEN items.item.value('text()[1]', 'varchar(300)') 
    END AS AppCode
FROM 
    @X.nodes('/ROOT/record/*') AS items(item)

结果不对-

ID       AppCode
-----------------
1        NULL
NULL     Code 1
2        NULL
NULL     Code 2

我需要这样的结果：

ID    Appcode
---------------
1     Code 1
2     Code 2
3     Code 3
4     Code 4
5     Code 5

Answer 1

更新2:这将是最好的方法。

试试看

WITH Numbers AS
(SELECT TOP(@X.value('count(//ID)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nmbr FROM master..spt_values)   
SELECT Nmbr 
      ,@X.value('(//ID[sql:column("Nmbr")])[1]','int') AS ID
      ,@X.value('(//AppCode[sql:column("Nmbr")])[1]','nvarchar(max)') AS AppCode
      ,@X.value('(//SomeMore[sql:column("Nmbr")])[1]','nvarchar(max)') AS SomeMore
FROM Numbers;

这个想法

• 计算in，并返回一个带有运行号的派生记分表(在本例中为1、2、3、4、5)。然后使用这个数字来根据元素的位置来定位它们。

还有两种方法，只是为了好玩:-)

这应该可以很快地与许多列一起使用：

DECLARE @X XML = '<ROOT>
                      <RECORD>
                          <ID>1</ID>
                          <AppCode>Code 1</AppCode>
                          <ID>2</ID>
                          <AppCode>Code 2</AppCode>
                          <ID>3</ID>
                          <AppCode>Code 3</AppCode>
                          <ID>4</ID>
                          <AppCode>Code 4</AppCode>
                          <ID>5</ID>
                          <AppCode>Code 5</AppCode>
                      </RECORD>
                  </ROOT>';
WITH allIDs AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Pos
          ,id.value('text()[1]','int') AS ID
    FROM @X.nodes('/ROOT/RECORD/ID') A(id)
)
SELECT ai.Pos
      ,ai.ID
      ,@X.value('/ROOT[1]/RECORD[1]/AppCode[sql:column("ai.Pos")][1]','nvarchar(max)') AS AppCode
      ,@X.value('/ROOT[1]/RECORD[1]/SomeMore[sql:column("ai.Pos")][1]','nvarchar(max)') AS SomeMore
FROM allIDs ai
ORDER BY ai.Pos;

这个想法是：

• 读取ID并找到它们的位置
• 通过sql:column()使用ID的位置读取相应的元素

更新:使用条件聚合的另一种方法

这应该更快：

WITH AllNodes AS
(
    SELECT (ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1) / 3 AS RowGroup
          ,nd.value('local-name(.)','nvarchar(max)') AS NodeName
          ,nd.value('text()[1]','nvarchar(max)') AS NodeValue
    FROM @X.nodes('/ROOT/RECORD/*') A(nd)
)
SELECT RowGroup
      ,MAX(CASE WHEN NodeName='ID' THEN NodeValue END) AS ID
      ,MAX(CASE WHEN NodeName='AppCode' THEN NodeValue END) AS AppCode
      ,MAX(CASE WHEN NodeName='SomeMore' THEN NodeValue END) AS SomeMore
FROm AllNodes
GROUP BY RowGroup

这个想法

• 我们将所有节点读取为EAV-结构。(ROW_NUMBER-1)/3 (整数除法！)将返回我们可以在GROUP BY中使用的组索引。其余的都是老式的支点。

两种方法的警告--

• 这需要一个没有缺少元素的结构。如果XML可能包括一个跟随节点缺失的部分，那么这是行不通的。
• ROW_NUMBER() OVER(ORDER BY (SELECT NULL))的解决方案在我使用过的所有情况下都适用于我。但值得一提的是，这并不能保证返回正确的立场。

Answer 2

假设这是您所追求的格式：

ID    Appcode
---------------
1     Code 1
2     Code 2

我就是这样做的：

DECLARE @X XML = '
<ROOT>
    <RECORD>
        <ID>1</ID>
        <AppCode>Code 1</AppCode>
        <ID>2</ID>
        <AppCode>Code 2</AppCode>
    </RECORD>
</ROOT>';


SELECT V.ID,AppCode
FROM @X.nodes('/ROOT/RECORD') AS X(R)
     CROSS APPLY (VALUES(X.R.value('(./ID/text())[1]','int'),X.R.value('(./AppCode/text())[1]','varchar(10)')),
                        (X.R.value('(./ID/text())[2]','int'),X.R.value('(./AppCode/text())[2]','varchar(10)'))) V(ID,AppCode)

然而，这是不会扩大的。所以如果你的ID是3，4，它不会给你更多的行。

Answer 3

如果您可以修改XML格式并按以下方式使用查询来获得结果

DECLARE @X XML = '<ROOT>
                  <RECORD>
                      <ID>1</ID>
                      <AppCode>Code 1</AppCode>
                  </RECORD>
                  <RECORD>                      
                      <ID>2</ID>
                      <AppCode>Code 2</AppCode>
                  </RECORD>
                  <RECORD>
                      <ID>3</ID>
                      <AppCode>Code 3</AppCode>
                  </RECORD>
                  <RECORD>
                      <ID>4</ID>
                      <AppCode>Code 4</AppCode>
                  </RECORD>
                  <RECORD>
                      <ID>5</ID>
                      <AppCode>Code 5</AppCode>
                  </RECORD>
              </ROOT>'


DECLARE @doc int
EXEC sp_xml_preparedocument @doc OUTPUT, @X

SELECT ID, AppCode
FROM OPENXML(@doc,'/ROOT/RECORD',2)
WITH (
ID int,
AppCode varchar(10)
)

输出会喜欢

ID  AppCode
1   Code 1
2   Code 2
3   Code 3
4   Code 4
5   Code 5