what should I change here in order to get both rows?

select * from openxml(
'<xfunc>
    <xfncode>01</xfncode>
    <description>Desc1</description>
    <xfunction>Func1</xfunction>
</xfunc>
<xfunc>
    <xfncode>02</xfncode>
    <description>Desc2</description>
    <xfunction>Func2</xfunction>
</xfunc>'
    ,'/xfunc')
with (r_XFNCode varchar(100) 'XFNCode',
    r_Description varchar(100) 'Description',
    r_XFunction varchar(100) 'XFunction'
)

Here for example with one single row I dont have any problem!

select * from openxml(
'<xfunc>
    <xfncode>01</xfncode>
    <description>Desc1</description>
    <xfunction>Func1</xfunction>
</xfunc>'
    ,'/xfunc')
with (r_XFNCode varchar(100) 'XFNCode',
    r_Description varchar(100) 'Description',
    r_XFunction varchar(100) 'XFunction'
)

asked 13 Sep, 07:54

Sarkis's gravatar image

Sarkis
547293764
accept rate: 20%

edited 13 Sep, 08:06

Volker%20Barth's gravatar image

Volker Barth
34.4k335490723


There are two errors AFAIK:

  • The XPath expressions in the WITH clause are case-sensitive, i.e. they must match the according nodes, and all XML tag names are case-sensitive by design.
  • Each XML document must have one single root node. Your second sample has one, the first has two, so you have to enclose both with a root/parent node, such as
select * from openxml(
'<root>
   <xfunc>
       <xfncode>01</xfncode>
       <description>Desc1</description>
       <xfunction>Func1</xfunction>
   </xfunc> 
   <xfunc>
       <xfncode>02</xfncode>
       <description>Desc2</description>
       <xfunction>Func2</xfunction>
   </xfunc>
</root>',
'//xfunc')
with (r_XFNCode varchar(100) 'xfncode',
    r_Description varchar(100) 'description',
    r_XFunction varchar(100) 'xfunction'
)

Note that the XPath expression in the OPENXML has been enhanced with the "descendant-or-self" specifier '//' to '//xfunc' to match xfunc nodes anywhere in the XML document.

This will return two rows with

01,Desc1,Func1
02,Desc2,Func2

Note, the name of the root node is arbitrary.

permanent link

answered 13 Sep, 08:50

Volker%20Barth's gravatar image

Volker Barth
34.4k335490723
accept rate: 33%

edited 13 Sep, 08:52

1)The example with a single row was working on my machine, but during copy paste has changed to small letters.

2)The problem is that I am getting the xml-content from a table called xfunc (using 'for xml' clause), and here I could not find anyway for the mentioned enclosing. The xml content used in my example was retrieved using this statement: select * from xfunc for xml auto, elements;

How should I change this select statement in order to have the result enclosed with root/parent node?

The table xfunc has three columns (xfncode, description, xfunction)

(13 Sep, 11:41) Sarkis
Replies hidden
Comment Text Removed
2

during copy paste has changed to small letters

That may indicate a [cough] issue with different platforms, different character sets, etcetera.

For example, I recently dealt with a problem where code that was copied and pasted and passed via email caused a SQL Anywhere syntax error even though it "looked perfect"... retyping it on the destination platform made it work.

That example MAY NOT HAVE ANYTHING TO DO with your problem, it is just an example of the THINGS THAT CAN GO WRONG crossing machine boundaries.

(14 Sep, 07:43) Breck Carter
Comment Text Removed

Thanks for the answer, do you have please any idea for my second question?

(16 Sep, 03:11) Sarkis

To cite the docs:

FOR XML AUTO does not return a well-formed XML document because the document does not have a single root node. If a <root> element is required, one way to insert one is to use the XMLELEMENT function. For example:

SELECT XMLELEMENT( NAME root,
                   (SELECT EmployeeID AS id, GivenName AS name
                   FROM Employees FOR XML AUTO ) );
(16 Sep, 03:14) Volker Barth

FWIW - and just to clarify: You seem to be trying to access a STP's result set from a different database on a different server. When the original data comes from a database and not a general web service, have you considered using the Remote Data Access facility (aka proxy tables) to access that? That would be way easier here than providing and consuming web services, methinks, and it includes access to remote stored procedures...

(16 Sep, 03:19) Volker Barth

Thanks, it has helped

(16 Sep, 06:45) Sarkis

So Remote Data Access would not work here for you? - Yep, call me curious...:)

(16 Sep, 07:43) Volker Barth
showing 2 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15

question asked: 13 Sep, 07:54

question was seen: 119 times

last updated: 16 Sep, 07:43