openxml with more than one row

[Baron]

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'
)

[VolkerBarth · Accepted solution]

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.

[Baron · Accepted solution]

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)

[Breck_Carter · Accepted solution]

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.

[Baron · Accepted solution]

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

[VolkerBarth · Accepted solution]

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 ) );

[VolkerBarth · Accepted solution]

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...

[Baron · Accepted solution]

Thanks, it has helped

[VolkerBarth · Accepted solution]

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