I'm receiving XML files as part of an EDI setup and I'm having an issues getting information from the child elements.
Here is the XML file:
<?xml version="1.0" encoding="UTF-8"?>
<cpi_event>
<header>
<source_tenant_uid>My_Coffee_Company_PM_Training</source_tenant_uid>
<event_type>receipt_received</event_type>
<event_uid>b631e1bb-d815-496c-bc50-6253f13b8d40</event_uid>
<event_created_at>2016-07-13 22:26:13.437613</event_created_at>
<destination_tenant_uid>My_Coffee_Company</destination_tenant_uid>
<document_type>My_Coffee_Company_OB_Receipt_Received_V1</document_type>
<document_uid>975_2239_b631e1bb-d815-496c-bc50-6253f13b8d40_0</document_uid>
</header>
<content>
<receipt>
<received_at>2016-07-13 15:26:13 -0700</received_at>
<reference_1>ddd</reference_1>
<reference_2>zzz</reference_2>
<customer>
<code>MYX001</code>
<name>My Coffee Company</name>
<uuid>5f17ba52-f408-43f7-9dab-f559e10c2b2f</uuid>
</customer>
<vendor>
<code>MY1200</code>
<name>My Coffee Company</name>
<uuid>a3c41ccf-a346-4cb0-8717-33dce5c4a3a5</uuid>
</vendor>
<receipt_items>
<receipt_item>
<code>017-001691</code>
<uuid>5e24c72b-da35-4b34-b8a6-5f00f5243a70</uuid>
<unit_quantity>500.0</unit_quantity>
<unit_of_measure>impressions</unit_of_measure>
<lot_code>A3</lot_code>
<expiry_date/>
<receive_to>
<code>Q-01-1-C</code>
<name>Q-01-1-C</name>
</receive_to>
</receipt_item>
<receipt_item>
<code>017-001692</code>
<uuid>5e24c72b-da35-4b34-b8a6-5f00f5243a71</uuid>
<unit_quantity>12500.5</unit_quantity>
<unit_of_measure>impressions</unit_of_measure>
<lot_code>A3</lot_code>
<expiry_date/>
<receive_to>
<code>CR15</code>
<name>Cups Racking 15</name>
</receive_to>
</receipt_item>
<receipt_item>
<code>017-001693</code>
<uuid>5e24c72b-da35-4b34-b8a6-5f00f5243a72</uuid>
<unit_quantity>25002.0</unit_quantity>
<unit_of_measure>impressions</unit_of_measure>
<lot_code>A2</lot_code>
<expiry_date/>
<receive_to>
<code>CR15FG</code>
<name>Cups Racking 15FG</name>
</receive_to>
</receipt_item>
<receipt_item>
<code>017-0016914</code>
<uuid>5e24c72b-da35-4b34-b8a6-5f00f5243a73</uuid>
<unit_quantity>25003.0</unit_quantity>
<unit_of_measure>impressions</unit_of_measure>
<lot_code>A1</lot_code>
<expiry_date/>
<receive_to>
<code>CR15A</code>
<name>Cups Racking 15A</name>
</receive_to>
</receipt_item>
</receipt_items>
</receipt>
</content>
</cpi_event>
I'm only concerned with the first step of my process which is extracting the <header> information and the <receipt_item> <uuid> for each item and then inserting that information into a header table in SQL.
Using the sample XML above I would end up with four rows being inserted into the SQL header table. Each row would contain the <header> information and the <receipt_item> <uuid> for one item.
What happens with my current code is it will grab the <header> info and only the last <receipt_item> <uuid>. I've changed my code around and have been able to get it to grab the <header> info and first <receipt_item> <uuid>, but not all four <receipt_item> <uuid>'s.
Here is my current SQL stored proceedure (called from SSIS with filename as input):
USE [PackManger_Sandbox]
GO
/****** Object: StoredProcedure [dbo].[EDI_XML_HEADER_Import] Script Date: 7/15/2016 3:45:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EDI_XML_HEADER_Import]
(
@XML_FILE NVARCHAR(MAX)
)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Event_Type as varchar(50)
-- Message log for logging
DECLARE @msg NVARCHAR(255)
-- Setup XML variable to be used to hold contents of XML file.
DECLARE @xml XML
-- The command line
DECLARE @COMMAND NVARCHAR(MAX)
-- The definition of the parameters used within the command line
DECLARE @PARAM_DEF NVARCHAR(500)
-- The parameter used to pass the file name into the command
DECLARE @FILEVAR NVARCHAR(MAX)
-- The output variable that holds the results of the OPENROWSET()
DECLARE @XML_OUT XML
SET @FILEVAR = @XML_FILE
SET @PARAM_DEF = N'@XML_FILE NVARCHAR(MAX), @XML_OUT XML OUTPUT'
SET @COMMAND = N'SELECT @XML_OUT = BulkColumn FROM OPENROWSET(BULK ''' + @XML_FILE + ''', SINGLE_BLOB) ROW_SET';
EXEC sp_executesql @COMMAND, @PARAM_DEF, @XML_FILE = @FILEVAR,@XML_OUT = @xml OUTPUT;
DECLARE @hdoc int
DECLARE @WatcherOutputEDIEventType int
DECLARE @sku_uuid nvarchar(100)
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
BEGIN TRY
-- Save event_type. Used to set return code which gets returned to SSIS to decide how to process XML
SELECT
@Event_Type = XMLhdr.event_type
FROM OPENXML (@hdoc, '/cpi_event/header', 2)
WITH (
event_type nvarchar(50)
) AS XMLhdr
IF @Event_Type = 'bom_updated' SET @WatcherOutputEDIEventType = 100;
IF @Event_Type = 'sku_created' SET @WatcherOutputEDIEventType = 200;
IF @Event_Type = 'sku_updated' SET @WatcherOutputEDIEventType = 250;
IF @Event_Type = 'receipt_received' SET @WatcherOutputEDIEventType = 300;
IF @Event_Type = 'job_reconciled' SET @WatcherOutputEDIEventType = 400;
IF @Event_Type = 'shipment_shipped' SET @WatcherOutputEDIEventType = 500;
IF @Event_Type = 'spare1' SET @WatcherOutputEDIEventType = 600;
IF @Event_Type = 'spare2' SET @WatcherOutputEDIEventType = 700;
IF @WatcherOutputEDIEventType > 0 AND @WatcherOutputEDIEventType < 251
-- Save SKU uuid
SELECT
@sku_uuid = XMLhdr.uuid
FROM OPENXML (@hdoc, '/cpi_event/content/sku', 2)
WITH (
uuid nvarchar(100)
) AS XMLhdr;
ELSE IF @WatcherOutputEDIEventType = 300
-- Save SKU uuid
SELECT
@sku_uuid = XMLhdr.uuid
FROM OPENXML (@hdoc, '/cpi_event/content/receipt/receipt_items/receipt_item', 2)
WITH (
uuid nvarchar(100)
) AS XMLhdr;
-- Update header table
INSERT INTO
PackManger_Sandbox.dbo.header
(
source_tenant_uid,
event_type,
event_uid,
event_created_at,
destination_tenant_uid,
document_type,
document_uid,
sku_uuid)
SELECT
source_tenant_uid = source_tenant_uid,
event_type = event_type,
event_uid = event_uid,
event_created_at = event_created_at,
destination_tenant_uid = destination_tenant_uid,
document_type = document_type,
document_uid = document_uid,
uuid = @sku_uuid
FROM OPENXML (@hdoc, '/cpi_event/header', 2)
WITH (
source_tenant_uid nvarchar(255),
event_type nvarchar(255),
event_uid nvarchar(255),
event_created_at datetime,
destination_tenant_uid nvarchar(255),
document_type nvarchar(255),
document_uid nvarchar(255));
-- Log success
SET @msg = 'Success updating header table.' + CONVERT(varchar(50), @Event_Type);
EXEC utility.Log_ProcedureCall_Sandbox
@ObjectID = @@PROCID,
@AdditionalInfo = @msg;
END TRY
BEGIN CATCH
-- Log error
SET @msg = 'Error updating header table.';
EXEC utility.Log_ProcedureCall_Sandbox
@ObjectID = @@PROCID,
@AdditionalInfo = @msg;
END CATCH
-- Remove the XML file from memory
EXEC sp_xml_removedocument @hdoc
RETURN @WatcherOutputEDIEventType
I'm trying to avoid looping and doing multiple SQL inserts.
Thanks!
