Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

traversing the XML structure to create OPENXML structure

Posted on 2005-04-07
13
Medium Priority
?
385 Views
Last Modified: 2013-11-19
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

0
Comment
Question by:rxraza
  • 6
  • 4
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13736496
>>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
 

Author Comment

by:rxraza
ID: 13736907
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13738161
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rxraza
ID: 13738349
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13738559
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
 

Author Comment

by:rxraza
ID: 13738674
<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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13740960
I would not do it that way, so I am afraid I have no idea.
0
 

Author Comment

by:rxraza
ID: 13742422
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 13747323
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13954663
I can take credit for this question.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question