Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Can't figure problem extracting via OpenXML()

4 posts in General Discussion (old) Last posting was on 2008-07-16 14:03:10.0Z
Bill Aumen Posted on 2008-07-15 18:41:31.0Z
From: "Bill Aumen" <delete_this_bill@aumenconsulting.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Can't figure problem extracting via OpenXML()
Lines: 83
Organization: Aumen Consulting Ltd
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <487cef5b$1@forums-1-dub>
Date: 15 Jul 2008 11:41:31 -0700
X-Trace: forums-1-dub 1216147291 10.22.241.152 (15 Jul 2008 11:41:31 -0700)
X-Original-Trace: 15 Jul 2008 11:41:31 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:95
Article PK: 866544

SQL Anywhere 10.0.1.3619



I am trying to learn XML processing with OpenXML(). From the Help file it
appears to be very flexible, and Breck's blog (OpenXML() Rocks) also speaks
to it's rocket-speed (The things I have come to expect from SQL Anywhere!)



After studying the Help files, I was successful in selecting an edge table
to view the contents.



Now I am using Breck's blog example to extract data. Seemed straight
forward. But I can't get mine to work.





XML snippet:

<?xml version="1.0" encoding="utf-8" ?>

- <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">

- <soap:Body>

- <PCCQueryResponse xmlns="http://aboriginaldev.mfc.gov.bc.ca/">

- <PCCQueryResult>

- <TRANSACTION xmlns="">

- <ORIGIN>

<APP_NAME>BP</APP_NAME>

<APP_VERSION>2.0.1</APP_VERSION>

.





Code executed in ISQL:

BEGIN

DECLARE lxml XML;

SET lxml =
xp_read_file('C:\TestData\pcc_name_search_results.xml');

SELECT * FROM openxml(lxml,
'/Envelope/Body/PCCQueryResponse/PCCQueryResult/TRANSACTION/ORIGIN' )

WITH (APP_NAME varchar(128) 'APP_NAME' )

;

END;





When I execute this code, I get 0 rows. Can you spot what I am doing wrong?





Thanks,

Bill


Matthew Young-Lai (Sybase iAnywhere) Posted on 2008-07-15 20:52:05.0Z
From: "Matthew Young-Lai \(Sybase iAnywhere\)" <none>
Newsgroups: sybase.public.sqlanywhere
References: <487cef5b$1@forums-1-dub>
Subject: Re: Can't figure problem extracting via OpenXML()
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <487d0df5$1@forums-1-dub>
Date: 15 Jul 2008 13:52:05 -0700
X-Trace: forums-1-dub 1216155125 10.22.241.152 (15 Jul 2008 13:52:05 -0700)
X-Original-Trace: 15 Jul 2008 13:52:05 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:96
Article PK: 866543

Here's a repost of an answer to a similar question.Matthew"Matthew Young-Lai
(Sybase iAnywhere)" <none@nowhere> wrote in message news:...> You need to
use a "*:" or a prefix with a matching uri on all the elements > that have
namespaces defined. So either this:> > select * from openxml( document, >
'/*:Envelope/*:Body/*:GetHRWOrdersResponse')> with (XMLOrderlist long
varchar '*:orderHRWList/text()')> > or this:> > select * from openxml(
document,'/*:Envelope/*:Body/p:GetHRWOrdersResponse', > 1, '<r
xmlns:p="http://ILSA.Engage.nl/webservices"/>' )> with (XMLOrderlist long
varchar 'p:orderHRWList/text()')> > > Matthew> > > "Majodi Ploegmakers"
<mploegmakers_at_inforit_dot_nl> wrote in message >
news:469d1807@forums-1-dub...>> The following works:>>>> select * from
openxml('<soap:Envelope >>
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" >>
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >>
xmlns:xsd="http://www.w3.org/2001/XMLSchema">>> <soap:Body>>>
<GetHRWOrdersResponse>>> <orderHRWList>list</orderHRWList>>>
<errorList>err</errorList>>> </GetHRWOrdersResponse>>>
</soap:Body></soap:Envelope>','/*:Envelope/*:Body/GetHRWOrdersResponse')>>
with (XMLOrderlist long varchar 'orderHRWList/text()')>>>> But the XML file
I need to handle has an extra xmlns and then the *: no >> longer works:>>>>
select * from openxml('<soap:Envelope >>
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" >>
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >>
xmlns:xsd="http://www.w3.org/2001/XMLSchema">>> <soap:Body>>>
<GetHRWOrdersResponse xmlns="http://ILSA.Engage.nl/webservices">>>
<orderHRWList>list</orderHRWList>>> <errorList>err</errorList>>>
</GetHRWOrdersResponse>>>
</soap:Body></soap:Envelope>','/*:Envelope/*:Body/GetHRWOrdersResponse')>>
with (XMLOrderlist long varchar 'orderHRWList/text()')>>>> How can I get the
second XML to work with openxml?>>>> Thanks,>>>> Majodi>> > >


Matthew Young-Lai (Sybase iAnywhere) Posted on 2008-07-16 13:14:56.0Z
From: "Matthew Young-Lai \(Sybase iAnywhere\)" <none>
Newsgroups: sybase.public.sqlanywhere
References: <487cef5b$1@forums-1-dub> <487d0df5$1@forums-1-dub>
Subject: Re: Can't figure problem extracting via OpenXML()
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <487df450$1@forums-1-dub>
Date: 16 Jul 2008 06:14:56 -0700
X-Trace: forums-1-dub 1216214096 10.22.241.152 (16 Jul 2008 06:14:56 -0700)
X-Original-Trace: 16 Jul 2008 06:14:56 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:97
Article PK: 866546

Here's another try. Hopefully the formatting is right this time. This was
originally posted 17 July 2007 in sybase.public.sqlanywhere.general.

Matthew


----

You need to use a "*:" or a prefix with a matching uri on all the elements
that have namespaces defined. So either this:

select * from openxml( document,
'/*:Envelope/*:Body/*:GetHRWOrdersResponse') with (XMLOrderlist long varchar
'*:orderHRWList/text()')

or this:

select * from openxml( document,'/*:Envelope/*:Body/p:GetHRWOrdersResponse',
1, '<r xmlns:p="http://ILSA.Engage.nl/webservices"/>' ) with (XMLOrderlist
long varchar 'p:orderHRWList/text()')


Bill Aumen Posted on 2008-07-16 14:03:10.0Z
From: "Bill Aumen" <delete_this_bill@aumenconsulting.com>
Newsgroups: sybase.public.sqlanywhere
References: <487cef5b$1@forums-1-dub> <487d0df5$1@forums-1-dub> <487df450$1@forums-1-dub>
Subject: Re: Can't figure problem extracting via OpenXML()
Lines: 38
Organization: Aumen Consulting Ltd
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <487dff9e$1@forums-1-dub>
Date: 16 Jul 2008 07:03:10 -0700
X-Trace: forums-1-dub 1216216990 10.22.241.152 (16 Jul 2008 07:03:10 -0700)
X-Original-Trace: 16 Jul 2008 07:03:10 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:98
Article PK: 866545

Thanks Matthew!

I was overlooking the namespace stuff. Your reply led me to discover: 1) the
technique I need to use, and 2) the XML file I am working with does not have
the namespaces defined correctly in the first place.

Thanks again,
Bill

"Matthew Young-Lai (Sybase iAnywhere)" <none> wrote in message
news:487df450$1@forums-1-dub...
> Here's another try. Hopefully the formatting is right this time. This
> was originally posted 17 July 2007 in sybase.public.sqlanywhere.general.
>
> Matthew
>
>
> ----
>
> You need to use a "*:" or a prefix with a matching uri on all the elements
> that have namespaces defined. So either this:
>
> select * from openxml( document,
> '/*:Envelope/*:Body/*:GetHRWOrdersResponse') with (XMLOrderlist long
> varchar '*:orderHRWList/text()')
>
> or this:
>
> select * from openxml(
> document,'/*:Envelope/*:Body/p:GetHRWOrdersResponse', 1, '<r
> xmlns:p="http://ILSA.Engage.nl/webservices"/>' ) with (XMLOrderlist long
> varchar 'p:orderHRWList/text()')
>
>