views:

40

answers:

1

So I'm trying to convert a web service that was an Oracle application to T-SQL. The fun part is I only have the web service code and no database code at all. I see there is an input parameter that contains <ROWSET><ROW NUM=\"1\"><TRANSACTIONID>123456</TRANSACTIONID></ROW></ROWSET>

I'm looking through the docs for T-SQL and I can't seem to find anything helpful on what I can do with this xml. From what I can understand it's used to insert values into a table since the elements will not be similar in every call.

Any help would be appreciated.

+2  A: 

Assuming this XML is a parameter to your T-SQL stored proc or function, you can use the OPENXML utility to work with this type of data. Here's a complete example (reposted here for clarity):

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<people>
  <person id="1">
    <firstname>John</firstname>
    <surname>Doe</surname>
  </person>
  <person id="2">
    <firstname>Mary</firstname>
    <surname>Jane</surname>
  </person>
</people>
'
/* Create an internal representation of the XML document */
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/people/person',1)
            WITH (id varchar(20),
                  firstname varchar(20) 'firstname',
                  surname varchar(20) 'surname')
EXECUTE sp_xml_removedocument @idoc

Result:

id  firstname       surname
1   John            Doe
2   Mary            Jane
dcp
Hmm. Will that work though if the sub elements are dynamic. For instance the firstname might be there or it might not, or perhaps there might be additional elements as well...?
Jisaak
@Jisaak - Yes, it will work fine. You will get NULL back for the elements that aren't present (e.g. take out <firstname>Mary</firstname> in my example above and re-run it to see this). If additional elements are present, they won't hurt anything.
dcp
Ok. I was trying to update a table using it and was having some problems. I'll keep it simple and go from there. Thanks!
Jisaak
Yep. Case-sensitivity:1 - Me:0. Now on to updating a table with these values.
Jisaak