导入“XML”节点到SQL与条件语句动态断绝(Import 'xml' node i

这涉及到这样一个问题：导入“XML”到SQL Server

你会如何处理可选XML节点，让我们说“IsFixed设定”是一个可选的节点，它在一个文件中存在，它不会在一个又一个，如何把状态代码为了不执行“Events.value存在“并避免空列？

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设定节点是不存在的，不要执行粗线：

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语句，但这样的不成功的应用情况：

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。

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节点不存在（动态）在所有我想要做的就是，怎么样？

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

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