T-Sql XML Nodes conditional based on node name

Question

I need to parse some XML to insert into a table, but my mid level node names can be one of two values <Image> or <Video>. I have an 'after insert' trigger to do this and here is my attempt, however it is not working. The area of interest is/are the CASE statements toward the bottom.

USE [cims]
GO
/****** Object:  Trigger [dbo].[tr_XML_Insert]    Script Date: 07/08/2014 16:52:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_XML_Insert]
ON [dbo].[ContainerXML]
AFTER INSERT
AS
BEGIN
    IF @@ROWCOUNT = 0
    RETURN
    SET NOCOUNT ON

    IF EXISTS(SELECT [XMLData] FROM INSERTED)
    BEGIN
        DECLARE @XML XML
        SELECT @XML = XMLData FROM [inserted]

        INSERT INTO [dbo].[ContainerImage]
        (
            CaptureDeviceCDIFID, CaptureDeviceName, Lat, Lon, DodAAC, OCR_Equipment, MOD_Equipment,
            TypeCode, SideImageComments, BackImageComments, SideImageCaptureDate, SideImage, 
            BackImageCaptureDate, BackImage, 
            RecordCreateDate
        )
        SELECT
            col.value('PCMU[1]/@cdifid[1]', 'NVARCHAR(15)'),
            col.value('PCMU[1]/@Name[1]', 'NVARCHAR(100)'),
            col.value('Location[1]/@lat[1]', '[DECIMAL](18,9)'),
            col.value('Location[1]/@lon[1]', '[DECIMAL](18,9)'),
            col.value('Location[1]/@DoDAAC[1]', 'NVARCHAR(10)'),
            REPLACE(col.value('BIC[1]/@ISOCode[1]', 'NVARCHAR(12)'), ' ', ''),
            REPLACE(col.value('BIC[1]/@ISOCode[1]', 'NVARCHAR(12)'), ' ', ''),
            col.value('BIC[1]/@TypeCode[1]', 'NVARCHAR(10)'),
            col.value('BIC[1]/@UserText1[1]', 'NVARCHAR(255)'),
            col.value('BIC[1]/@UserText2[1]', 'NVARCHAR(255)'),
            CASE xmldata.col.value('local-name(/*[1])','varchar(20)')
                WHEN 'Image' THEN col.value('Image[1]/@imagetime[1]', 'DATETIME')
                WHEN 'Video' THEN col.value('Video[1]/@time[1]', 'DATETIME')
            END,
            CASE xmldata.col.value('local-name(/*[1])','varchar(20)')
                WHEN 'Image' THEN col.value('Image[1]/@imageData[1]', 'VARBINARY(MAX)')
                WHEN 'Video' THEN col.value('Video[1]/@videoData[1]', 'VARBINARY(MAX)')
            END,
            CASE xmldata.col.value('local-name(/*[1])','varchar(20)')
                WHEN 'Image' THEN col.value('Image[2]/@imagetime[1]', 'DATETIME')
                WHEN 'Video' THEN col.value('Video[2]/@time[1]', 'DATETIME')
            END,
            CASE xmldata.col.value('local-name(/*[1])','varchar(20)')
                WHEN 'Image' THEN col.value('Image[2]/@imageData[1]', 'VARBINARY(MAX)')
                WHEN 'Video' THEN col.value('Video[2]/@videoData[1]', 'VARBINARY(MAX)')
            END,
            GETDATE()
            FROM @XML.nodes('//Containers/Container') as xmldata(col)                   
    END
END

Here are the 2 format for XML files - I can easily do one or the other, I am looking for a way to do it within the same code. I essentially want everything in the XML, I just do not know how to say "if node name == Image, get these fields, else if node name == Video, get these fields

<Containers>
    <Container>
        <PCMU  cdifid="81.135.189.71" Name="Test PCMU1" />
        <Location  lat="38.35688" lon="-77.45752" 
                DoDAAC="TODO??" />
        <BIC    time="2014-06-20T15:11:07"
            idStatus="OK"
            ISOCode="(null)"
            TypeCode="0x0"
            UserText1="??"
            UserText2="??" />
        <Video  view="Container Side" 
            time="2014-06-20T15:11:07" 
            videoData="big chunk of base64 data"/>
        <Video  view="Container Rear" 
            time="2014-06-20T15:11:07" 
            videoData="big chunk of base64 data" />
    </Container>
</Containers>


<Containers>
    <Container>
        <PCMU  cdifid="81.135.189.71" Name="Test PCMU1" />
        <Location  lat="38.35688" lon="-77.45752" 
                DoDAAC="TODO??" />
        <BIC    time="2014-06-20T15:11:07"
            idStatus="OK"
            ISOCode="(null)"
            TypeCode="0x0"
            UserText1="??"
            UserText2="??" />
        <Image  view="Container Side" 
            imageTime="2014-06-20T15:11:07" 
            imageData="big chunk of base64 data"/>
        <Image  view="Container Rear" 
            imageTime="2014-06-20T15:11:07" 
            imageData="big chunk of base64 data" />
    </Container>
</Containers>

You should to post a sample of the XML you want to parse and the expected output of the select statement using that XML. One other thing is that you assume the trigger is called once for each inserted row. That is not the case. If you insert more than one row at a time you will only get one call to the trigger and the pseudo table inserted will have multiple rows. Instead of assigning the XML to a variable you should use inserted in the query with a cross apply on XMLData.nodes(...)... — Mikael Eriksson
– Mikael Eriksson, Commented Jul 9, 2014 at 5:50
Thank you Mikael. I do not work in TSQL a lot, so I will google cross apply and get smart about it — hooverbox
– hooverbox, Commented Jul 9, 2014 at 12:52

Mikael Eriksson · Accepted Answer · 2014-07-09 13:31:35Z

You need to use the insert table since you can have more than one row inserted at a time and your case statements can be rewritten to use two xPath expressions in one value clause.

CREATE TRIGGER [dbo].[tr_XML_Insert]
ON [dbo].[ContainerXML]
AFTER INSERT
AS
BEGIN
  INSERT INTO [dbo].[ContainerImage]
  (
      CaptureDeviceCDIFID, CaptureDeviceName, Lat, Lon, DodAAC, OCR_Equipment, MOD_Equipment,
      TypeCode, SideImageComments, BackImageComments, SideImageCaptureDate, SideImage, 
      BackImageCaptureDate, BackImage, 
      RecordCreateDate
  )
  SELECT
      col.value('(PCMU/@cdifid)[1]', 'NVARCHAR(15)'),
      col.value('(PCMU/@Name)[1]', 'NVARCHAR(100)'),
      col.value('(Location/@lat)[1]', '[DECIMAL](18,9)'),
      col.value('(Location/@lon)[1]', '[DECIMAL](18,9)'),
      col.value('(Location/@DoDAAC)[1]', 'NVARCHAR(10)'),
      REPLACE(col.value('(BIC/@ISOCode)[1]', 'NVARCHAR(12)'), ' ', ''),
      REPLACE(col.value('(BIC/@ISOCode)[1]', 'NVARCHAR(12)'), ' ', ''),
      col.value('(BIC/@TypeCode)[1]', 'NVARCHAR(10)'),
      col.value('(BIC/@UserText1)[1]', 'NVARCHAR(255)'),
      col.value('(BIC/@UserText2)[1]', 'NVARCHAR(255)'),
      xmldata.col.value('(Image/@imageTime, Video/@time)[1]','DATETIME'),
      xmldata.col.value('(Image/@imageData, Video/@videoData)[1]','VARBINARY(MAX)'),
      xmldata.col.value('(Image/@imageTime, Video/@time)[2]','DATETIME'),
      xmldata.col.value('(Image/@imageData, Video/@videoData)[2]','VARBINARY(MAX)'),
      GETDATE()
      FROM inserted AS I
        CROSS APPLY I.XMLData.nodes('/Containers/Container') as xmldata(col)                   
END

thank you Mikael, that worked beautifully and was exactly what I was after

Collectives™ on Stack Overflow

T-Sql XML Nodes conditional based on node name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related