mysql - 如何在表中加载动态 XML 数据

Question

我有以下 XML。我想动态加载到临时表中。当我尝试以静态方式加载它时。现在，当我尝试使用枢轴动态执行此操作时，它加载得很好，它只给出一行(不是全部是最大值或最小值。)

XML1:

'<ArrayOpp>
  <Opp>
    <ID>1251</ID>
    <Type>Testing</Type>
    <OppoType>Other</OppoType>
    <CategoryName>Testing1</CategoryName>
    <SubCategoryName>Testing1</SubCategoryName>
    <Effort>1200.00</Effort>
  </Opp>
    <Opp>
    <ID>1251</ID>
    <Type>Testing</Type>
    <OppoType>Other</OppoType>
    <CategoryName>Testing2</CategoryName>
    <SubCategoryName>Testing2</SubCategoryName>
    <Effort>1200.00</Effort>
  </Opp>
  </ArrayOpp>'

结果:

ID  Type    OppoType    CategoryName    SubCategoryName Effort
1   Testing Other       Testing1        Testing1        1000
2   Testing Other       Testing2        Testing2        2000

如果 XML 添加一个节点:Cost

XML:2[在此处输入图像描述][1]

'<ArrayOpp>
  <Opp>
    <ID>1251</ID>
    <Type>Testing</Type>
    <OppoType>Other</OppoType>
    <CategoryName>Testing1</CategoryName>
    <SubCategoryName>Testing1</SubCategoryName>
    <Effort>1200.00</Effort>
    <Cost>12.00</Cost>
  </Opp>
    <Opp>
    <ID>1251</ID>
    <Type>Testing</Type>
    <OppoType>Other</OppoType>
    <CategoryName>Testing2</CategoryName>
    <SubCategoryName>Testing2</SubCategoryName>
    <Effort>1200.00</Effort>
    <Cost>12.00</Cost>
  </Opp>
  </ArrayOpp>'

结果:

ID  Type    OppoType    CategoryName    SubCategoryName Effort Cost
1   Testing Other       Testing1        Testing1        1000   12.00
2   Testing Other       Testing2        Testing2        2000   12.00

那么你能告诉我我该怎么做吗？？？

Answer 1

可能是我弄错了，但我看不出任何需要动态方法(动态sql？)，也不需要PIVOT()。

这对两者都适用，没有任何问题:

DECLARE @mockup TABLE(ID INT IDENTITY, Comment VARCHAR(100),TheXml XML);

INSERT INTO @mockup VALUES
('XML1','<ArrayOpp>
  <Opp>
    <ID>1251</ID>
    <Type>Testing</Type>
    <OppoType>Other</OppoType>
    <CategoryName>Testing1</CategoryName>
    <SubCategoryName>Testing1</SubCategoryName>
    <Effort>1200.00</Effort>
  </Opp>
    <Opp>
    <ID>1251</ID>
    <Type>Testing</Type>
    <OppoType>Other</OppoType>
    <CategoryName>Testing2</CategoryName>
    <SubCategoryName>Testing2</SubCategoryName>
    <Effort>1200.00</Effort>
  </Opp></ArrayOpp>')
,('XML2','<ArrayOpp>
  <Opp>
    <ID>1251</ID>
    <Type>Testing</Type>
    <OppoType>Other</OppoType>
    <CategoryName>Testing1</CategoryName>
    <SubCategoryName>Testing1</SubCategoryName>
    <Effort>1200.00</Effort>
    <Cost>12.00</Cost>
  </Opp>
    <Opp>
    <ID>1251</ID>
    <Type>Testing</Type>
    <OppoType>Other</OppoType>
    <CategoryName>Testing2</CategoryName>
    <SubCategoryName>Testing2</SubCategoryName>
    <Effort>1200.00</Effort>
    <Cost>12.00</Cost>
  </Opp>
  </ArrayOpp>');

SELECT m.ID
      ,m.Comment 
      ,op.value('(ID/text())[1]','int') AS opp_ID
      ,op.value('(Type/text())[1]','nvarchar(max)') AS opp_Type
      ,op.value('(OppoType/text())[1]','nvarchar(max)') AS opp_OppoType
      ,op.value('(CategoryName/text())[1]','nvarchar(max)') AS opp_CategoryName
      ,op.value('(SubCategoryName/text())[1]','nvarchar(max)') AS opp_SubCategoryName
      ,op.value('(Effort/text())[1]','decimal(10,4)') AS opp_Effort
      ,op.value('(Cost/text())[1]','decimal(10,4)') AS opp_Cost
FROM @mockup m
CROSS APPLY m.TheXml.nodes('ArrayOpp/Opp') A(op);