|
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'
)
|
|
There are two errors AFAIK:
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. 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 '19, 11:41)
Baron
Replies hidden
Comment Text Removed
2
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 '19, 07:43)
Breck Carter
Comment Text Removed
Thanks for the answer, do you have please any idea for my second question?
(16 Sep '19, 03:11)
Baron
To cite the docs:
SELECT XMLELEMENT( NAME root,
(SELECT EmployeeID AS id, GivenName AS name
FROM Employees FOR XML AUTO ) );
(16 Sep '19, 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 '19, 03:19)
Volker Barth
Thanks, it has helped
(16 Sep '19, 06:45)
Baron
So Remote Data Access would not work here for you? - Yep, call me curious...:)
(16 Sep '19, 07:43)
Volker Barth
|