使用条件语句将'xml'节点动态导入到SQL Server中

问题描述

这与以下问题有关:将"xml"导入Sql Server

This is related to this question:Import 'xml' into Sql Server

您将如何处理可选的XML节点，比如说"IsFixed"是一个可选节点，它存在于一个文件中，而在另一个文件中不存在，如何将条件放入代码中以便不执行"Events.value"并避免使用Null列?

How would you deal with optional XML node, let's say "IsFixed" is an optional node and it exists in one file and it doesn't exist in another one, how to put condition in the code in order not to execute "Events.value" and avoid having Null column?

DECLARE @XML XML = '
<EventSchedule>
    <Event Uid="2" Type="Main Event">
        <EventKind>MainEvent</EventKind>
        <Fields>
            <Parameter Name="Type" Value="TV_Show"/>
            <Parameter Name="Name" Value="The Muppets"/>
            <Parameter Name="Duration" Value="00:30:00"/>
        </Fields>
    </Event>
    <Event Uid="3" Type="Secondary Event">
        <EventKind>SecondaryEvent</EventKind>
        <Fields>
            <Parameter Name="Type" Value="TV_Show"/>
            <Parameter Name="Name" Value="The Muppets II"/>
            <Parameter Name="Duration" Value="00:30:00"/>
        </Fields>
    </Event>
</EventSchedule>'

如果IsFixed节点不存在，请不要执行粗体行:

Don't execute the bold line if IsFixed node is not there:

SELECT
    EventUID = Events.value('@Uid', 'int'),
    EventType = Events.value('@Type', 'varchar(20)'),
    ***EventIsFixed = Events.value('(IsFixed)[1]', 'varchar(20)'),***
    EventKind = Events.value('(EventKind)[1]', 'varchar(20)'),
    ParameterType = Events.value('(Fields/Parameter[@Name="Type"]/@Value)[1]', 'varchar(20)'),
    ParameterName = Events.value('(Fields/Parameter[@Name="Name"]/@Value)[1]', 'varchar(20)'),
    ParameterDuration = Events.value('(Fields/Parameter[@Name="Duration"]/@Value)[1]', 'varchar(20)')
FROM
    @XML.nodes('/EventSchedule/Event') AS XTbl(Events)

我试图在SELECT语句中使用CASE WHEN，但这样失败:

I tried to use CASE WHEN in SELECT statement but unsuccessful like this:

SELECT
    EventUID = Events.value('@Uid', 'int'),
    EventType = Events.value('@Type', 'varchar(20)'),
    CASE
        WHEN Events.value('(IsFixed)[1]', 'varchar(20)') IS NOT NULL
        THEN ''
    END AS EventIsFixed,
    EventKind = Events.value('(EventKind)[1]', 'varchar(20)'),
    ParameterType = Events.value('(Fields/Parameter[@Name="Type"]/@Value)[1]', 'varchar(20)'),
    ParameterName = Events.value('(Fields/Parameter[@Name="Name"]/@Value)[1]', 'varchar(20)'),
    ParameterDuration = Events.value('(Fields/Parameter[@Name="Duration"]/@Value)[1]', 'varchar(20)')
FROM
    @XML.nodes('/EventSchedule/Event') AS XTbl(Events)

上面的代码仍然将EventIsFixed列创建为空值(输出表).

the above code still creates a column as EventIsFixed with empty value (the output table).

EventUID    EventType   EventIsFixed    EventKind   ParameterType   ParameterName   ParameterDuration
2           Main Event                  MainEvent      TV_Show      The Muppets     00:30:00
3           Secondary Event             SecondaryEvent  TV_Show     The Muppets II  00:30:00

我想做的是当该XML节点存在时在表中具有该列，而当XML节点不存在时(动态地)根本不具有该列，怎么办?

What I want to do is to have the column in the table when that XML node exists and not to have the column at all when the XML node doesn't exists (dynamically), how?

推荐答案

使用exist()检查是否存在IsFixed.

IF @XML.exist('/EventSchedule/Event/IsFixed') = 1
BEGIN
  SELECT
      EventUID = Events.value('@Uid', 'int'),
      EventType = Events.value('@Type', 'varchar(20)'),
      EventIsFixed = Events.value('(IsFixed)[1]', 'varchar(20)'),
      EventKind = Events.value('(EventKind)[1]', 'varchar(20)'),
      ParameterType = Events.value('(Fields/Parameter[@Name="Type"]/@Value)[1]', 'varchar(20)'),
      ParameterName = Events.value('(Fields/Parameter[@Name="Name"]/@Value)[1]', 'varchar(20)'),
      ParameterDuration = Events.value('(Fields/Parameter[@Name="Duration"]/@Value)[1]', 'varchar(20)')
  FROM
      @XML.nodes('/EventSchedule/Event') AS XTbl(Events)
END
ELSE
BEGIN
  SELECT
      EventUID = Events.value('@Uid', 'int'),
      EventType = Events.value('@Type', 'varchar(20)'),
      EventKind = Events.value('(EventKind)[1]', 'varchar(20)'),
      ParameterType = Events.value('(Fields/Parameter[@Name="Type"]/@Value)[1]', 'varchar(20)'),
      ParameterName = Events.value('(Fields/Parameter[@Name="Name"]/@Value)[1]', 'varchar(20)'),
      ParameterDuration = Events.value('(Fields/Parameter[@Name="Duration"]/@Value)[1]', 'varchar(20)')
  FROM
      @XML.nodes('/EventSchedule/Event') AS XTbl(Events)
END

这篇关于使用条件语句将'xml'节点动态导入到SQL Server中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！