Hi all,

having no luck importing a structured XML file into a datawindow
using PowerBuilder I wonder if and how that may be done in
ASA (V >= ASA 9). For the time being it would be enough to get
the data from an XML document like the following as a resultset:

<?xml version="1.0" encoding="utf-8"?>
<ORDER xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.healy-hudson.com/XMLSchema/Schlecker/1.1"
xsi:schemaLocation="http://www.healy-hudson.com/XMLSchema/Schlecker/1.1
file:///c:/Development/Ordering%20V3/V3.NET/Schlecker/Schlecker.Schemas/orderraw.xsd">
<ORDER_HEADER>
<ORDER_ID>BNR SL1</ORDER_ID>
...
</ORDER_HEADER>
<ORDER_ITEM_LIST>
<TASK>
<CUSTOMER_ORDER_ID>61616</CUSTOMER_ORDER_ID>
<DELIVERY_DATE>2008-12-30T06:00:50.153</DELIVERY_DATE>
<CUSTOMER_ID>4711</CUSTOMER_ID>
</TASK>
<TASK>
<CUSTOMER_ORDER_ID>99999</CUSTOMER_ORDER_ID>
<DELIVERY_DATE>2008-12-30T06:00:50.153</DELIVERY_DATE>
<CUSTOMER_ID>4712</CUSTOMER_ID>
</TASK>
</ORDER_ITEM_LIST>
</ORDER>

As you see the <TASK> structure occurs more than one time. I believe that
may be possible using OPENXML but don't be sure how to start and if that
can work.

Thanks for any hint,

Chris Werner
f+s software gmbh

I never tried it myself, but I'd like to point you to Breck's Blog. This
entry
http://sqlanywhere.blogspot.com/2008...xml-rocks.html
might give you some insights.

HTH
Reimer

Chris Werner wrote:

Hello Reiner,

thanks, that's a good explanation from Breck. It rocks
like expected. But I can't get it to work anyway, I get
the error message "Expected Prolog" in character 1,
line 1 and column 1. Don't know what exactly that
means because my XML document has some kind of
prolog like the one used as a sample in Brecks article.

That's what I'm trying as a first step:

ALTER PROCEDURE "DBA"."p_test_xml1"()
result(ORDER_ID varchar(16))
begin
declare @xml long varchar;
declare @rslt numeric(10);
set @xml=xp_read_file('E:\\tmp\\Test.xml');
select trim(ORDER_ID) from
openxml(@xml,'/ORDER/ORDER_HEADER') with(ORDER_ID varchar(16)
'ORDER_ID')
end


What may be wrong?

TIA

Chris Werner
f+s software gmbh

"R. Pods" <r.pods (AT) gmx (DOT) net> schrieb im Newsbeitrag
news:4b4b1db6$1 (AT) forums-1-dub (DOT) ..

Your procedure worked ok for me (11.0.1.2280). The only thing I can think
of is to check your xp_read_file() call to make sure the xml file was read
into your variable.

--
Jason Hinsperger
Product Manager
Sybase


"Chris Werner" <cwAT{PleaseNoSpam}f-s.de> wrote

Hi Jason,

thanks for your answer. That's odd. I've already checked xp_read_file()
and it reads the right xml code. But I'm trying that in ASA 9.0.2, maybe
I should try a 11.0.1 engine.

Thanks again

Chris Werner
f+s software gmbh

"Jason Hinsperger" <jason_hinsperger (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:4b4c9949 (AT) forums-1-dub (DOT) ..

Hi,

so I checked that using SA 11.0.1 and get the same result:

XML-Parser error: Zeichen: 1, Zeile: 1, Spalte: 1
Prolog expected
SQLCODE=-888, ODBC 3 State="HY000"

I doublechecked if the file is read correctly. So I've no clue whats
going on there. I append the XML document so maybe someone
could have a look what may be wrong with it?

TIA
Chris Werner
f+s software gmbh


"Chris Werner" <cwAT{PleaseNoSpam}f-s.de> schrieb im Newsbeitrag
news:4b4cacb6$1 (AT) forums-1-dub (DOT) ..

Hi Chris,
The XML file you attached contains '-' characters outside of the XML tags.
If I remove them everything works fine with your file and the stored proc.
you posted earlier.

--Jason Hinsperger
Product Manager
Sybase



"Chris Werner" <cw (AT) f-s (DOT) de> wrote

Hi Jason,

thanks for your answer. As far as I can see the document doesn't contain
dashes. They are shown when you open it using a web browser for reasons
not really clear to me. When you open the document in an editor there are
no dashes.

Best regards,

Chris Werner
f+s software gmbh

"Jason Hinsperger" <jason_hinsperger (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:4b4f5a52$1 (AT) forums-1-dub (DOT) ..

Chris,
xmlns="http://www.healy-hudson.com/XMLSchema/Schlecker/1.1" defines a
default namespace.

According to 'the rules', any element that is subordinate to <ORDER> is
defined within this namespace context. It is not legal in openxml to address
elements as if they had no namespace when, in fact they do, ie:
/ORDER/ORDER_HEARER - presumes NO namespace, so you can't use this

/*:ORDER/*:ORDER_HEADER - allows matching of any namespace


Therefore, the following should work for you:
ALTER PROCEDURE "DBA"."p_test_xml1"()
result( ORDER_ID varchar(16) )
begin
declare @xml long varchar;
declare @rslt numeric(10);
set @xml = xp_read_file('c:\temp\sampledoc.xml');
// 2 check xp_read_fiel only:
set @rslt = xp_write_file('c:\temp\SampleDocTest.xml',@xml);
select trim(ORDER_ID)
from openxml(@xml,'/*:ORDER/*:ORDER_HEADER') with(ORDER_ID varchar(16)
'*:ORDER_ID')
END
;
select * from p_test_xml1()


If you don't want to use the wild-card you could provide a namespace
argument to the OpenXML() function:
declare @ns varchar(255);
set @ns = '<ORDER xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xsi:schemaLocation="http://www.healy-hudson.com/XMLSchema/Schlecker/1.1
file:///c:/Development/Ordering%20V3/V3.NET/Schlecker/Schlecker.Schemas/orderraw.xsd"
xmlns:foo="http://www.healy-hudson.com/XMLSchema/Schlecker/1.1"
/ >';
select trim(ORDER_ID)
from openxml(@xml,'/foo:ORDER/foo:ORDER_HEADER',@ns) with(ORDER_ID
varchar(16) 'foo:ORDER_ID')

Yes, XML and XPath is overly complicated and gross...

--Jason Hinsperger
Product Manager
Sybase


"Chris Werner" <cw (AT) f-s (DOT) de> wrote