asked on

traversing the XML structure to create OPENXML structure

Hi folks:

I have the following code. What I want to do is to pass in the XML structure to some user defined function and returns a string back. I want to make a generic user defined function but it appears that there exists no such thing that will traverse the XML dom and and returns child node and parents. Just like we can traverse the DOM thru System.XML namespace.

so the following input to a user defined function should return me the following string avoiding the necessary HARCODING inside select * from OPENXML. Still we can have one user defined function per XML but that is not desirable - more code to maintain

group_id='100' AND order_number='1025'

set @doc = '<Criteria>
      <Group_id>100</Group_id>
      <Order_Number>1025</Order_Number>
</Criteria>'

exec sp_xml_preparedocument @idoc OUTPUT, @doc

select * from
openxml (@idoc , '/Criteria' , 2)
      With (Group_ID int 'Group_id' ,
            Order_Number int 'Order_Number' )
where Group_id = 100 and Order_Number = 1025

Anthony Perkins

>>so the following input to a user defined function should return me the following string avoiding the necessary HARCODING inside select * from OPENXML<<
First of all, you cannot use OPENXML in a user defined function you will have to use a stored procedure.
Secondly, you do not need to hard code the XPath or the element names you can pass them in just like any other parameter.

rxraza

ASKER

Thanks for letting me know that OPENXML can't be used inside User defined function. Regarding passing parameters to the sored procedure is concerned, yes, we can always do that, but the thing is like suppose I am building a dynamic sql statement from the Stored Proc and I want to pass the where clause criteria. For the following XML

<Table1 key="primarykey">
      <Field1 type="1">value 1</Field1>
      <Field2 type="2">value 2</Field2>
      <Fieldn type="1">value 3</Fieldn>
</Table1>
<Table2 key="primarykey">
      <Field1 type="1">value 1</Field1>
      <Field2 type="2">value 2</Field2>
      <Fieldn type="1">value 3</Fieldn>
</Table2>
<Table3 key="primarykey">
      <Field1 type="1">value 1</Field1>
      <Field2 type="2">value 2</Field2>
      <Fieldn type="1">value 3</Fieldn>
</Table3>

I will have to create the following parameter list to the stored procedure

table1Name,
table2Name,
table3Name,
Table1Field1Name,
Table1Field2Name,
Table1FieldnName,
Table2Field1Name,
Table2Field2Name,
Table2FieldnName,
Table3Field1Name,
Table3Field2Name,
Table3FieldnName

and then it is not going to be good for 'n' number of fields, So if I go this way, there will be a lot of proliferation of stored procedures, maintainability cost would be high, Alternatively I need a way to create a dynamic SQL from a stored procedure by passing a generic list of parameters, one way could be to pass encoded strings but I do not know if that is a better solution.

Anthony Perkins

You lost me. But let me explain how we do it here. We pass in one input parameter (and optionally return one output parameter). The input parameter is an Xml Document that has a set root structure. You can then query that struncture and it can drive all the other parameters.

Hope this helps, otherwise feel free to ask.

rxraza

ASKER

>>You can then query that struncture and it can drive all the other parameters.

Can we send the query in a generic manner like

Give me the root element.
Give me the first child of the root element etc etc

if Yes, then I would appreciate if you paste some code snippet to show that.

Anthony Perkins

Supposing your Xml structure has a root of Request than you can include elements or attributes that indicate the XPath to other elements and attributes. For example:

<Request XPath="/Orders/Order/@orderNumber>
      <Orders>
            <Order orderNumber="12345678"/>
            <Order orderNumber="23456789"/>
            <Order orderNumber="34567890"/>
            <Order orderNumber="45678901"/>
            <Order orderNumber="56789012"/>
      </Orders
</Request>

You can then OPENXML to get the XPath attribute, save that in a variable and then OPENXML again with that variable to get other elements.

Not sure if that is what you mean.

rxraza

ASKER

<Request XPath="/Orders/Order/@orderNumber>
<Orders>
<Order orderNumber="12345678"/>
</Orders
</Request>

Suppose the above is an input to a stored procedure what I want as an output is the following string

orderNumber='12345678'

How would I do it?

Anthony Perkins

I would not do it that way, so I am afraid I have no idea.

rxraza

ASKER

aceperkins:

Thanks for the post so far. If possible can you please recommend the best way of doing it.

The problem is given some input to a stored procedure most probably in XML form how can we transform it into some stirng that will serve as the where clause criteria for some SQL SELECT statement.

<input>
<field1>some value for field 1</field1>
<field2>some value for field 2</field2>
</input>

the output should be
field1='some value for field1' AND field2='some value for field 2'

I hope I am not wasting your valuable time. thanks

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

I can take credit for this question.