on 2019 Sep 13 8:54 AM
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
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.
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 ) );
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...
User | Count |
---|---|
67 | |
10 | |
10 | |
10 | |
10 | |
8 | |
8 | |
7 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.