Read XML-DBfield and use content as BOD in ION

Philipp Sailer · 2025-03-19T14:22:59+00:00

There was an error rendering this rich post.

hello,

via StoredProc I am reading an SQL DB-field (datatype XML) which contains a full BOD message (ie. Sync.Itemmaster). Through ION EnterpriseConnector I am able to access it by a connectionpoint. The call of the storedproc is ok (i am also writing a timestamp in a separate field of the according record, so that i know the storedproc was called).
in the Connection Point I set the document type accordingly, but the read Content is not processed in ION. I would also have built a test dataflow which should write this BOD out as a file anywhere else. But i don't see anything in Oneview or in the ErrorBods or elsewhere.
has anybody experience with such an implementation?
does it Need a Special data conversion in the storedproc before returning it to ION?

Help very appreciated.
thanks
philipp

Find more posts tagged with

Accepted answers

Kevin Heiman

I setup a test table and inserted a SyncSalesOrder into the myXML column and other values into the remaining to test with.

The Procedure allow test of the columns or the XML column

There are many ways to manipulate XML, so a few extra examples in the code in regard to this.

But both A and B paths worked in ION Desk.
ION documentation with examples is at https://docs.infor.com/inforos/2024.x/en-us/useradminlib_cloud/default.html?helpcontent=iontechconceag_cloud_osm/lsm1436532387183.html

**Apologies but did not see a good way to insert the code that did not manipulate it…

And the reply is anchored above the conversation..



-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		<Author,,Name>

-- Create date: <Create Date,,>

-- Description:	<Description,,>

-- =============================================

CREATE OR ALTER PROCEDURE myXMLSync

	 (@parmAorB varchar(1)

	, @parmProcessedFrom varchar(5)

	, @parmAction varchar(10)

	, @xmldataso xml = null output

	)

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	--

	DECLARE @currentDateTime as datetime;

	--DECLARE @XMLOuterEnvelope XML = '<?xml version="1.0" encoding="utf-8"?><DataArea></DataArea>'

	SET @currentDateTime = CURRENT_TIMESTAMP;

	 --

	 --  Put record into a in process state

	WITH myXMLTableTop1 AS

	( 

	SELECT TOP 1 * 

		FROM dbo.myXMLTable 

		WHERE myProcessedFlag = 0 

		ORDER BY myTimeStamp ASC

	)

	UPDATE myXMLTableTop1

		SET myProcessedFlag = 9, myUpdateDateTime = @currentDateTime ;

	--

	--  choose a method to build XML either from (A)the columns excluding myXML  OR (B) the myXML Column 

	 IF (@parmAorB = 'A')

		BEGIN

			SET @xmldataso = ( 

				SELECT TOP 1

						myIndex as IndexMy

					  , myReference as ReferenceMy

					  , myText as TextMy

					  , myDecimal as DecimalMy

					FROM dbo.myXMLTable as XMLTableMy

					WHERE myProcessedFlag = 9

  					FOR XML AUTO, ELEMENTS, root('DataArea')	

					)

		END

	ELSE

		BEGIN

			SET @xmldataso = ( 

				SELECT TOP 1 myXML 

					FROM dbo.myXMLTable 

					WHERE myProcessedFlag = 9

  					)

			SET @xmldataso = @xmldataso.query('/SyncSalesOrder/DataArea')

		/*	SET @xmldataso = ( 

				SELECT TOP 1 myXML.query('/SyncSalesOrder/DataArea') 

					FROM dbo.myXMLTable 

					WHERE myProcessedFlag = 9

  					)

		*/

	--		SET @xmldataso.modify('insert sql:variable("@xmlOuterEnvelope")   into (/DataArea)[1] ')

	--		SELECT 'B', @xmldataso

		END

     --

	 IF (@parmAction = 'UPD')

		BEGIN

			 UPDATE dbo.myXMLTable 

					SET myProcessedFlag = 1 

					, myProcessedFrom = @parmProcessedFrom

					, myUpdateDateTime = @currentDateTime 

					WHERE myProcessedFlag = 9 

		END

	 --

	Select @xmldataso

	RETURN; 

END

GO

All comments

Kevin Heiman

Hi Philipp. Can you clarify what your requirement is?
If the data column is a XML type and you want to return just that or is it a fragment and you need to wrap some XML around it, or something else such as FOR XML AUTO, or ?
If the data is a full XML OAGIS BOD, then you could also consider a Application Connector using IO Box technique. This offers benefits of full message Header support as well as other benefits.
What occurs in ION when you test the SP?
What have you setup your connection point for the Statement? Can you share the SQL or parts of it?
Have tried something simple against one of the examples and it works? Or is this all new and first time?

Philipp Sailer

Hi Kevin,

first i tried to just write a full BOD message out from ION to the database field (XML type) - successful, but only with the <DataArea> section.

second i wanted to read this back into ION just to test if it generally works - it did not. then i thought i need to enrich this XML with the heads to get a full XML OAGIS BOD. for testing i did this manually, but also this test without success.

I am not 100% sure about Application Connector. we have a 3rd party implementation here and this software only writes XML structures in DB fields XML type. they can adjust the content so that it becomes a full OAGIS BOD. but they use only the database for data exchange. or do i missunderstand something with the Application Connector?

i have already done ION<>DB integrations in the past successfully, with read and write SPs. but not yet with full XML-structures as final BODs.

i try to show my current test setting:

DB-table:

CREATE TABLE [dbo].[Test_XML](
[ID] [int] IDENTITY(1,1) NOT NULL,
[XMLdata] [xml] NULL,
[importdate] [datetime] NULL,
[readdate] [datetime] NULL
)

the call of the SP in ION:

declare @xmldata xml;
EXEC ReadXML @xmldata output;

the SP (simplified):

begin	
        UPDATE	[dbo].[Test_XML]

	set readdate = getdate()

	WHERE readdate IS NULL



	set @xmldbdata = (select XMLdata from [dbo].[Test_XML] WHERE readdate IS NULL)


	return



end

thanks,

philipp

Kevin Heiman

re the write out to a table;

Only the Data area of the BOD is passed to the stored procedure. So that would be what is expected. The data can be repeating "Multi-BOD" but I think you are on referencing a Single BOD. When you write that you can write it to tables columns vs. as a single XML. You can do that with variables or with direct XML node references.

….. @xmlData.nodes('/DataArea[1]/MyIndex[sql:variable("@IndexCount")]') as V(text1)   …)				

UPDATE myTable  SET  mID = @XMLID

				  ,text1 = @XMLtext

				  ,date1 = @XMLdate1



		WHERE myKey= @XMLmyKey;

Or you can use SQL XML functions to modify , append, removed data in the XML.
I would also consider IOBox for this scenario if you are wanting XML. Then you get the headers , archiving, purging as a plus. It depends on what is next.
Inversely that IOBox can be used for the data from the remote application into ION.
In the end, are you content with the data to the endpoint?
I will comment on your Inbound topics in separate reply.

Kevin Heiman

A quick observation. If you set all the readate that are Null to a getdate value, then the ensuing SELECT Where readdate IS NULL will fail to find anything.
Note that you may have more than one row returned as well, so you need to handle that scenario with mulit BOD or single BOD.
ION will put the Data Area into a OAGIS BOD envelope (add an Application Area with content and an outside node name as the document type you used) for you.

Philipp Sailer

thanks for the input. sure thing, above was just a quick sample for testing purposes.

but even if i would select one specific record (by ID) through the SP, ION is not retrieving anything.

Philipp Sailer

thanks for further explanation especially in regards to MultiBOD. i think i am able to manage this.

more important would be the code snippet on how i can get the ION return from DB as XML running. did i miss something from your last comment as you mentioned to comment this separately?

thanks again.

Kevin Heiman

I was awaiting verification that the NULL was not the cause of your issue. Have you setup a test scenario where you use a traditional FOR XML form a table.. with just one column? Just to validate it is not something else.
I do believe using the XML Column as a Data area fragment should work as the FOR XML is to a XML data type as well. I will try to setup that test in next days.
You can review SQL logs and Ent Connector logs as well.

Philipp Sailer

sorry missunderstood. the NULL is not the cause of the issue. initially i started with only the the XML column. the other fields were added later for analysis and testing purposes. it would be great if you could also do some tests on your end. i will have a look on the logs as well.

Kevin Heiman

I setup a test table and inserted a SyncSalesOrder into the myXML column and other values into the remaining to test with.

The Procedure allow test of the columns or the XML column

There are many ways to manipulate XML, so a few extra examples in the code in regard to this.

**Apologies but did not see a good way to insert the code that did not manipulate it…

And the reply is anchored above the conversation..



-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		<Author,,Name>

-- Create date: <Create Date,,>

-- Description:	<Description,,>

-- =============================================

CREATE OR ALTER PROCEDURE myXMLSync

	 (@parmAorB varchar(1)

	, @parmProcessedFrom varchar(5)

	, @parmAction varchar(10)

	, @xmldataso xml = null output

	)

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	--

	DECLARE @currentDateTime as datetime;

	--DECLARE @XMLOuterEnvelope XML = '<?xml version="1.0" encoding="utf-8"?><DataArea></DataArea>'

	SET @currentDateTime = CURRENT_TIMESTAMP;

	 --

	 --  Put record into a in process state

	WITH myXMLTableTop1 AS

	( 

	SELECT TOP 1 * 

		FROM dbo.myXMLTable 

		WHERE myProcessedFlag = 0 

		ORDER BY myTimeStamp ASC

	)

	UPDATE myXMLTableTop1

		SET myProcessedFlag = 9, myUpdateDateTime = @currentDateTime ;

	--

	--  choose a method to build XML either from (A)the columns excluding myXML  OR (B) the myXML Column 

	 IF (@parmAorB = 'A')

		BEGIN

			SET @xmldataso = ( 

				SELECT TOP 1

						myIndex as IndexMy

					  , myReference as ReferenceMy

					  , myText as TextMy

					  , myDecimal as DecimalMy

					FROM dbo.myXMLTable as XMLTableMy

					WHERE myProcessedFlag = 9

  					FOR XML AUTO, ELEMENTS, root('DataArea')	

					)

		END

	ELSE

		BEGIN

			SET @xmldataso = ( 

				SELECT TOP 1 myXML 

					FROM dbo.myXMLTable 

					WHERE myProcessedFlag = 9

  					)

			SET @xmldataso = @xmldataso.query('/SyncSalesOrder/DataArea')

		/*	SET @xmldataso = ( 

				SELECT TOP 1 myXML.query('/SyncSalesOrder/DataArea') 

					FROM dbo.myXMLTable 

					WHERE myProcessedFlag = 9

  					)

		*/

	--		SET @xmldataso.modify('insert sql:variable("@xmlOuterEnvelope")   into (/DataArea)[1] ')

	--		SELECT 'B', @xmldataso

		END

     --

	 IF (@parmAction = 'UPD')

		BEGIN

			 UPDATE dbo.myXMLTable 

					SET myProcessedFlag = 1 

					, myProcessedFrom = @parmProcessedFrom

					, myUpdateDateTime = @currentDateTime 

					WHERE myProcessedFlag = 9 

		END

	 --

	Select @xmldataso

	RETURN; 

END

GO

Kevin Heiman

checking back to see if that helped.

Kevin Heiman

sorry, I now see your reply earlier. great to hear progress. Anything you can share with the community as well? and again … sorry about the out of sequence posting…

Philipp Sailer

not really anything additional to share with the community. your code samples as well as the link to the documentation (ION Technology Connectors Admin) is very helpful and brings the necessary understanding of the DB integration.

thanks again.