upload Userview datasource uStore

[Simon Knott]

Hi All

I was wondering if you can help me with this at all.

I have created a product where I am using a UserView to pull out the data from an excel list of a list of people with a table number. 

Name 1, 1

Name 2, 1

Name 3, 1

Name 4, 2

Name 5, 2

etc...

Example

UserView 1: "TABLE 1" "SELECT * FROM @[CLASSROOM] WHERE @[CLASSROOM].[Table] = 1"

UserView 2: "TABLE 2" "SELECT  * FROM @[CLASSROOM] WHERE @[CLASSROOM].[Table] = 2"

etc..

It all works fine but I need to make this available on uStore and also allow the datasource that the UserView is using to be the one the customer uploads, is this possible? I have created the product on uStore and when allowing datasource upload it ignores whats uploaded and uses what is stored on the uProduce server.

Any help would be appreciated.

Regards

Simon Knott

[Wayne]

Hi Simon,

uStore sends Recipient lists as an XML file data-source to uProduce.

I don't think you can use a XML file as a User View data-source.

Regards,

Wayne

[eko...@gmail.com]

Simon,

We recently attempted this task for two of our customers and it was confirmed by Support that it is not possible. The crux of the disconnect is that uStore uses a dynamically named XML file (or a SQL table for an XM Campaign) and uPlan doesn't know how to query the correct data source.

[Igor Vorobeychik]

First uPlan can use xml in particular MS persistence format  https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/xml-persistence-format.

Second uStore will send exact same format to production , so uProduce use exact same format.

Third uStore saves uploaded files in a different XML format.

And last , Plan file you can use XML  for a UserView , just the way to do it via variable and variable XML will be of particular structure described in uPlan User Guide https://help.xmpie.com/uPlan/v.9.4/Help/en/index.htm#t=Work_with_Add_Plan_Objects%2FDefining_a_User_View_from_an_XML_String.htm&rhsearch=userview%20xml&rhhlterm=userview%20xml&rhsyns=%20

I used this technique many times , for example using HTML Generic you can allow user to add records and pass xml via variable to the plan.

Igor.

On Tue, Mar 19, 2019 at 10:20 AM eko...@gmail.com <eko...@gmail.com> wrote:

Simon,

We recently attempted this task for two of our customers and it was confirmed by Support that it is not possible. The crux of the disconnect is that uStore uses a dynamically named XML file (or a SQL table for an XM Campaign) and uPlan doesn't know how to query the correct data source.

--
You received this message because you are subscribed to the Google Groups "XMPie Interest Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xmpie-users...@googlegroups.com.
To post to this group, send email to xmpie...@googlegroups.com.
Visit this group at https://groups.google.com/group/xmpie-users.
For more options, visit https://groups.google.com/d/optout.

[Simon Knott]

Igor

Do you have an example of this in action that you can share?

Would that mean that the customer would have to export the data source as an XML file?

Regards

Simon Knott

[Igor Vorobeychik]

uStore has no plug-in to allow upload data file via Dial.

Hence custom control will be needed to do similar thing like recipient list , transform uploaded file into XML.

I do not have example , but you can just use example from uPlan doc , expose variable as campaign dial and pass XML as a text ...

XMPie PS services can write such plug-in and offer as a service ...

Igor. 

[Igor Vorobeychik]

As a side note , our Global Asset repository use UserView from XML when it comes for multi asset selection .

So at one selection you may select all images for a flyer  instead of having X placeholders ...

[Igor]

After  I was contacted by Ed as well read carefully original post I'd like to say the following.

1) All my comments about XML , User view  ... are correct 

2)  Problem is in a way you are trying to use it or you can say a way uPlan works or uStore submits job to production 

in uPlan when you  use Userview  you have defined a reference to a Datasource , yes it is same as your recipient datasource but from uPlan prospective it is different 

Your user view will work 

Now your question why it does not work via uStore , the answer is simple because uStore only updates RecipientList source and does not  affects Datasource one , that is why you see things from original Job that used for proof or process .

if we lets say remove datasource reference in the uPlan 

Our Userview will give error Error: line 1 character 0: The SQL query/queries could not be validated since the Plan does not define a Data Source.

if we will add additional datasource we will get an error Error: line 1 character 0: An SQL query could not be validated since it does not specify a Data Source for execution. 

Meaning we need instruct UserView what Datasource to use to run query against .

Unfortunately I do not know  how to reference RecipeintList datasource, I'm checking if such special   name exists (BTW for User View from XML XMPie has a special datasource name  @{EMBEDED_XML}) 

Now lets assume XMPie does not have such as well uStore will not change the way it  submits job to production , so how we can still use customer uploaded Recipient List as a datasource in a User View ?

I can give a direction on what can be done , there are 3 ways :

1)  Use technique   User view from XML and update varibale with XML from uploaded list , problem that if list is big XML is huge as well Customization steps are prior RL and you need to write update statement   on at least finalize step ... 

2) Create your campaign but reference   uStore as a datasource for your USerview , pass via variable OrderProductID so you know what  recipientList and selected XML is in use and run query against it ...

On Tuesday, March 19, 2019 at 11:19:43 AM UTC-4, Igor wrote:

First uPlan can use xml in particular MS persistence format  https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/xml-persistence-format.

Second uStore will send exact same format to production , so uProduce use exact same format.

Third uStore saves uploaded files in a different XML format.

And last , Plan file you can use XML  for a UserView , just the way to do it via variable and variable XML will be of particular structure described in uPlan User Guide https://help.xmpie.com/uPlan/v.9.4/Help/en/index.htm#t=Work_with_Add_Plan_Objects%2FDefining_a_User_View_from_an_XML_String.htm&rhsearch=userview%20xml&rhhlterm=userview%20xml&rhsyns=%20

I used this technique many times , for example using HTML Generic you can allow user to add records and pass xml via variable to the plan.

Igor.

[Igor Vorobeychik]

so in reality your plan will have 2 additional datasources and in your UserView you will place a logic what datasource and query to run depends if request comes from uStore or from uPlan or just job executed on uProduce 

[Igor Vorobeychik]

So once you pass OrderProductID from uStore you should be able to get  to RecipinetListID and from RecipientList table path to SourceXML that will look like  'recipients\saved\User_1340\4726ba68-5d4e-42ed-a4ea-c52995073834.xml' , Now you will have to use OPENROWSET to get data from it  

Here example of the select from this file that takes in consideration utf-16 ... and select  that   will give you records 

DECLARE @XML XML

set @XML = (SELECT CONVERT(xml, REPLACE(BulkColumn,'utf-16','utf-8'), 2) FROM  

    OPENROWSET(Bulk'D:\XMPie\uStore\App\uStoreShared\recipients\saved\User_1340\4726ba68-5d4e-42ed-a4ea-c52995073834.xml', SINGLE_CLOB)[rowsetresults])

SELECT  

       Tbl.Col.value('State[1]', 'nvarchar(2)') as [State] ,

              Tbl.Col.value('First_x0020_Name[1]', 'nvarchar(20)') as[First Name],

              Tbl.Col.value('Last_x0020_Name[1]', 'nvarchar(20)') as[Last Name]


FROM    @XML.nodes('//RecipitentList') Tbl(Col)

 

 I suggest write a store procedure  so your sql inPlan will be simple 

Good luck 

[eko...@gmail.com]

Thank you for the input and direction

I was able to solve the puzzle end to end. See attached proof. The Process PDF output also worked as intended when processed from the uStore Admin Orders tab.

Few important points:

• The SQL syntax got a bit tricky, but it finally worked. As Igor mentioned, the SQL script needed to be created as a Stored Procedure. 
• The proof of concept that I built only has one column, but it can be easily expanded to add other columns. I'm sure someone can make the code more dynamic to accept input parameters for a variable number of columns.
• I also wrote a SKIP ADOR to ignore all records except the first record since we don't need the rest of the records as extra pages.
  
• Since the server I tested on also has an Extension sever, I had to specify the IP address (or the device name) where the SQL server lives. At first I used localhost and some of the proofs were failing.

Let me know if you need some help connecting the wires.

uPlan is attached.

Here's the Stored Proc.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: <Ed Kotnik>

-- Author: <www.121services.com> 

-- Create date: <2019/04/03>

-- Description: <Uploaded list in uStore returns a table>

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

CREATE PROCEDURE RecipientListToTable 

-- Add the parameters for the stored procedure here

@inOrderProductID int

AS

BEGIN

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

-- interfering with SELECT statements.

SET NOCOUNT ON;

--First we build the filename for the XML file on the disk using the OrderProductID as input

DECLARE @FileOnDisk NVARCHAR(256) = (SELECT 'F:\XMPie\uStore\App\uStoreShared\' + RecipientList.SourceXML As FileOnDisk

FROM OrderProduct INNER JOIN RecipientList ON OrderProduct.RecipientListID = RecipientList.RecipientListID

WHERE OrderProductID = @inOrderProductID)

--The we read the file from the disk as XML and surface as a table

DECLARE @sql nvarchar(max)

SELECT @sql=

'DECLARE @XML XML = 

(SELECT CONVERT(xml, REPLACE(BulkColumn,''utf-16'',''utf-8''), 2)

FROM OPENROWSET(Bulk'''+@FileOnDisk+''', SINGLE_CLOB)[rowsetresults])

SELECT  

   Tbl.Col.value(''Column1[1]'', ''nvarchar(30)'') as [Column1]  --add more columns can be returned separated by commas

FROM @XML.nodes(''//RecipitentList'') Tbl(Col)

'

 exec(@sql)

END

GO

To unsubscribe from this group and stop receiving emails from it, send an email to xmpie...@googlegroups.com.