Import 'xml' node into sql sever dynamical

问题:

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

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>'

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)

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)

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

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?

回答1:

Check for the existence of IsFixed using 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