如何在表中加载动态xml数据

ozxc1zmp 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(413)

我有下面的xml。我想动态加载到temp表中。当我试图以静态方式加载它时。当我尝试用pivot动态加载它时，它只给出一行（max或min值不是全部）
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

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

sql mysql sql-server tsql

来源：https://stackoverflow.com/questions/53742040/how-to-load-dynamic-xml-data-in-table

2条答案

按热度按时间

ekqde3dh1#

可能我弄错了，但我看不出有什么必要使用动态方法（dynamicsql？），这两种方法也不需要 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);

赞(0）回复(0）举报 2021-06-18

drnojrws2#

尝试以下操作将xml数据加载到临时表：
静载荷
对于动态负载，请尝试以下方法：
动载荷

赞(0）回复(0）举报 2021-06-17