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.

query for xml

2 posts in General Discussion Last posting was on 2009-02-11 19:55:22.0Z
Liza A.S Posted on 2009-02-03 06:18:41.0Z
Sender: 35eb.4987c10c.1804289383@sybase.com
From: Liza A.S
Newsgroups: ianywhere.public.general
Subject: query for xml
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4987e1c1.39a4.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 2 Feb 2009 22:18:41 -0800
X-Trace: forums-1-dub 1233641921 10.22.241.41 (2 Feb 2009 22:18:41 -0800)
X-Original-Trace: 2 Feb 2009 22:18:41 -0800, 10.22.241.41
Lines: 69
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7346
Article PK: 5763

Hi,

I am using query A to get data from XML document A and
succesfully insert the data into database.

Query A

.
.
.
select @varCategory=upper(EFMemComCategory),
@varVariety=upper(EFMemComVariety),
@varQuantity=EFMemComQty,
@varUnit=upper(EFMemComUnit)
from OPENXML(@varXMLString,'/Commodity/Group')
with ( CommodityCategory varchar(255) 'Category',
CommodityVariety varchar(255) 'Variety',
CommodityQty float 'Quantity',
CommodityUnit varchar(100) 'Unit' )
.
.
.

XML document A
<Commodity>
<Group>
<Category>Vegetables</Category>
<Variety>Spinach</Variety>
<Quantity>1</Quantity>
<Unit>MT</Unit>
</Group>
<Group>
<Category>Vegetables</Category>
<Variety>Cabbage</Variety>
<Quantity>2</Quantity>
<Unit>MT</Unit>
</Group>
<Group>
<Category>Fruits</Category>
<Variety>Apple</Variety>
<Quantity>1</Quantity>
<Unit>MT</Unit>
</Group>
</Commodity>

But how my query should like if the XML document is like
below?
<Commodity>
<Group>
<Category>Vegetables</Category>
<Variety>Spinach</Variety>
<Quantity>1</Quantity>
<Unit>MT</Unit>
<Category>Vegetables</Category>
<Variety>Cabbage</Variety>
<Quantity>2</Quantity>
<Unit>MT</Unit>
</Group>
<Group>
<Category>Fruits</Category>
<Variety>Apple</Variety>
<Quantity>1</Quantity>
<Unit>MT</Unit>
</Group>
</Commodity>

Any help is very much appreciated.

Thanks in advance.


Kurt Lichtner Posted on 2009-02-11 19:55:22.0Z
From: "Kurt Lichtner" <kurt.reply.to.group@domain.does.not.exist.com>
Newsgroups: ianywhere.public.general
References: <4987e1c1.39a4.1681692777@sybase.com>
Subject: Re: query for xml
Lines: 124
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49932d2a$1@forums-1-dub>
Date: 11 Feb 2009 11:55:22 -0800
X-Trace: forums-1-dub 1234382122 10.22.241.152 (11 Feb 2009 11:55:22 -0800)
X-Original-Trace: 11 Feb 2009 11:55:22 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7355
Article PK: 5770

In the second case, your XML isn't structured in a hierarcical way that is
meaningful to OPENXML. The docs say:

http://dcx.sybase.com/index.php#http%3A%2F%2Fdcx.sybase.com%2F1100en%2Fdbreference_en11%2Ffunctions-s-4946531.html

"If a WITH clause expression matches more than one node, then only the first
node in the document order is used."

and in your case you have multiple matching tags under <Group>. Given what
I interpret your data to mean,
your XML should be structured something like:

<Commodity>
<Group>
<Category>Vegetables
<Variety>Spinach
<Quantity>1</Quantity>
<Unit>MT</Unit>
<Quantity>1</Quantity>
</Variety>

<Variety>Cabbage
<Quantity>2</Quantity>
<Unit>MT</Unit>
</Variety>
</Category>
<Category>Vegetables
<Variety>Apple
<Quantity>1</Quantity>
<Unit>MT</Unit>
</Variety>
</Category>
</Group>
</Commodity>


The query:

select *
from OPENXML(doc,'/Commodity/Group/Category/Variety')
with ( CommodityCategory varchar(255) '../text()',
CommodityVariety varchar(255) 'text()',
CommodityQty float 'Quantity',
CommodityUnit varchar(100) 'Unit' )

should give you the kind of results you're looking for.

Hope this helps.

--
Kurt Lichtner
Sybase iAnywhere R&D, SQL Anywhere Core Server

<Liza A.S> wrote in message news:4987e1c1.39a4.1681692777@sybase.com...
> Hi,
>
> I am using query A to get data from XML document A and
> succesfully insert the data into database.
>
> Query A
>
> .
> .
> .
> select @varCategory=upper(EFMemComCategory),
> @varVariety=upper(EFMemComVariety),
> @varQuantity=EFMemComQty,
> @varUnit=upper(EFMemComUnit)
> from OPENXML(@varXMLString,'/Commodity/Group')
> with ( CommodityCategory varchar(255) 'Category',
> CommodityVariety varchar(255) 'Variety',
> CommodityQty float 'Quantity',
> CommodityUnit varchar(100) 'Unit' )
> .
> .
> .
>
> XML document A
> <Commodity>
> <Group>
> <Category>Vegetables</Category>
> <Variety>Spinach</Variety>
> <Quantity>1</Quantity>
> <Unit>MT</Unit>
> </Group>
> <Group>
> <Category>Vegetables</Category>
> <Variety>Cabbage</Variety>
> <Quantity>2</Quantity>
> <Unit>MT</Unit>
> </Group>
> <Group>
> <Category>Fruits</Category>
> <Variety>Apple</Variety>
> <Quantity>1</Quantity>
> <Unit>MT</Unit>
> </Group>
> </Commodity>
>
> But how my query should like if the XML document is like
> below?
> <Commodity>
> <Group>
> <Category>Vegetables</Category>
> <Variety>Spinach</Variety>
> <Quantity>1</Quantity>
> <Unit>MT</Unit>
> <Category>Vegetables</Category>
> <Variety>Cabbage</Variety>
> <Quantity>2</Quantity>
> <Unit>MT</Unit>
> </Group>
> <Group>
> <Category>Fruits</Category>
> <Variety>Apple</Variety>
> <Quantity>1</Quantity>
> <Unit>MT</Unit>
> </Group>
> </Commodity>
>
> Any help is very much appreciated.
>
> Thanks in advance.