CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-openxml
Amanda_Panda
Amanda_Panda

Reputation: 1186

Using OpenXML to pass in multiple params to a SQL Server stored procedure

Is there a way to use OpenXML with a SQL Server stored procedure to pass in multiple parameters without using a temp table (which has really been slowing us down?)?

For example, with a single parameter:

exec usp_FooBar 
'<Data><States><State StateID="16" StateName="Massachusetts" "/></States></Data>'

In usp_FooBar I'd get the StateID value by

Declare @StateID int
.
.
.

 SELECT @StateID = StateID FROM  
  OPENXML(@IDOC,'/Data/States/State',1)   
  WITH      
  (      
   StateID INT    
  )

However, lets say I want to pass in multiple states:

exec usp_FooBar 
'<Data><States><State StateID="16" StateName="Massachusetts" "/><State StateID="87" StateName="Minnesota" /></States></Data>'

Is there any way I can do something similar to the single state scenario, where I can accept any arbitrary number of parameters, without using temp tables (which as I've mentioned, have taken a significant hit on performance for me)?

Upvotes: 0

Views: 370

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Example

Declare @XML xml = '
<Data>
    <States>
        <State StateID="16" StateName="Massachusetts" />
        <State StateID="25" StateName="New York" />
        <State StateID="13" StateName="Rhode Island" />
    </States>
</Data>
'


Select Grp    = r.value('@StateID','int')
      ,Item   = attr.value('local-name(.)','varchar(100)')
      ,Value  = attr.value('.','varchar(max)') 
 From  @XML.nodes('/Data/States/State') as A(r)
 Cross Apply A.r.nodes('./@*') AS B(attr)

Returns

Grp Item        Value
16  StateID     16
16  StateName   Massachusetts
25  StateID     25
25  StateName   New York
13  StateID     13
13  StateName   Rhode Island

Upvotes: 1

Related Questions