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

rxrazaAsked:
Who is Participating?
 
Anthony PerkinsCommented:
>>field1='some value for field1' AND field2='some value for field 2'<<
Are we talking about using dynamic SQL? If so I do not use it for security and performance reasons.  So I am not a good person to ask on this.  However, since you want both the Column Name and Value you may be able to do something like this:

<Request>
    <Params name="field1" value="some value for field1"/>
    <Params name="field2" value="some value for field2"/>
    ... rest of the Xml document

</Request>

You can then use OpenXml to get all the individual parameters and concatenate appropriately.

Alternatively, you could pass the whole Where clause as one attribute:

<Request where="field1='some value for field1' AND field2='some value for field 2">
    ... rest of the Xml document

</Request>

But again, I don't do it this way, so there may be a better way of doing this.
0
 
Anthony PerkinsCommented:
>>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.

0
 
rxrazaAuthor Commented:
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.


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
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.
0
 
rxrazaAuthor Commented:
>>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.

0
 
Anthony PerkinsCommented:
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.
0
 
rxrazaAuthor Commented:
<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?


0
 
Anthony PerkinsCommented:
I would not do it that way, so I am afraid I have no idea.
0
 
rxrazaAuthor Commented:
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
0
 
Anthony PerkinsCommented:
I can take credit for this question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.