如何插入不同的XML字段值表中的行

问题描述：

declare @Message as xml 

set @Message='<message> 

     <body> 
     <ID>1</ID> 
     <setup_time>10</setup_time> 
     <prod_cycle_time>10</prod_cycle_time> 
     <unit>cas</unit> 
     <Flag>NULL</Flag> 
     <FillingPO>NULL</FillingPO> 
     <PackAtCAN1>NULL</PackAtCAN1> 
     </body> 
     </message>'

从上面的XML结构需要插入几个字段数据如下格式提到

ID  Desc    Value 
    1  setup_time  10 
    1  prod_cycle_time 10 
    1  unit    Case 
    1  Flag    NULL

答

看起来像你想从XML中获取值，然后做一个unpivot。

使用value()函数从XML中提取值，您可以使用union all来执行unpivot。

declare @Message as xml 

set @Message=' 
<message> 
    <body> 
    <ID>1</ID> 
    <setup_time>10</setup_time> 
    <prod_cycle_time>10</prod_cycle_time> 
    <unit>cas</unit> 
    <Flag>NULL</Flag> 
    <FillingPO>NULL</FillingPO> 
    <PackAtCAN1>NULL</PackAtCAN1> 
    </body> 
</message>' 

declare @ID int 
set @ID = @Message.value('(/message/body/ID/text())[1]', 'int') 

select @ID as ID, 
     'setup_time' as [Desc], 
     @Message.value('(/message/body/setup_time/text())[1]', 'nvarchar(50)') as Value 
union all 
select @ID, 
     'prod_cycle_time', 
     @Message.value('(/message/body/prod_cycle_time/text())[1]', 'nvarchar(50)') 
union all 
select @ID, 
     'unit', 
     @Message.value('(/message/body/unit/text())[1]', 'nvarchar(50)') 
union all 
select @ID, 
     'Flag', 
     @Message.value('(/message/body/Flag/text())[1]', 'nvarchar(50)')

答

您也可以使用XQuery首先改变你的XML：

declare @transform xml 

set @transform = @message.query(' 
    let $capture := ("setup_time","prod_cycle_time","unit","Flag") 

    for $item in /message/body/* 
    let $id := $item/../ID 
    where $capture = local-name($item) 
    return <Row> 
      <ID>{$id/text()}</ID> 
      <Desc>{local-name($item)}</Desc> 
      <Value>{$item/text()}</Value> 
     </Row> 
') 

SELECT x.value('ID[1]','int') AS ID, 
     x.value('Desc[1]','varchar(max)') AS [Desc], 
     x.value('Value[1]','varchar(max)') AS Value 
FROM @transform.nodes('/Row') tmp(x)

+1我喜欢看到这样的例子（和XML-DML的），因为我知道，有趣的和有用的东西都可以通过FLWOR完成，但不要使用它/ XML足以知道所有可能性。这是SQL Server内置的真正未充分利用和低估的功能，它增加了在数据库中存储XML（合理）的合法性，与那些“你永远不应该将非结构化数据存储在单一字段中”的人相呼应:)。 – 2014-10-31 16:06:55

如何插入不同的XML字段值表中的行

相关推荐