Import 'xml' node into sql sever dynamically with conditional statement

Question

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?

score 2 · Accepted Answer · answered Sep 12 '14 at 08:05

2

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

answered Sep 12 '14 at 08:05

Mikael Eriksson

136,425
22
210
281

thanks Mikael for quick response. A follow up question is that I have many xml files with 30 nodes "required" and 60 nodes "optional", so using your if-else statement for 60 optional nodes (i.e. dynamically appear in xml files) would require 60! (60 factorial) = 8.3209871e+81 if-else cases. is there any other way to address this problem? thank you. – Tempo Sep 12 '14 at 08:35
@Tempo Then you need to parse the XML and build the query dynamically. – Mikael Eriksson Sep 12 '14 at 08:41
can you please give me more hints or sample code like the above you provided. I still have to have the table constructed because of other logic already built in the DB. thanks. – Tempo Sep 12 '14 at 08:53
@Tempo You can have a look [here](http://stackoverflow.com/a/23268527/569436) for one example. – Mikael Eriksson Sep 12 '14 at 08:56
@Tempo Here is another one. http://stackoverflow.com/questions/25640784/how-to-select-all-column-in-xml/25658290#25658290 – Mikael Eriksson Sep 12 '14 at 08:57
wow! that was an amazing link with xml parsing. I tried to run the code but I did not get anything (no errors), just got "command completed successfully" with no output. was I supposed to get similar dynamic query as in the link? – Tempo Sep 12 '14 at 09:22

Import 'xml' node into sql sever dynamically with conditional statement

1 Answers1

Linked