SQLServerCentral Article

Using XML to pass and return values to procs

,

It has always been difficult to pass a variable number of parameters to stored procs. In SQL Server 2005, XML can be a solution to this problem.

We have a Routing and Approval Web-Service application, into which is passed a series of XML requests and it returns XML responses to a WebSphere client. The XML passed in may consist of from one to hundreds of items to be routed for approval. We have come up with some ways to make it easy to parse this data and send back XML in the expected format.

First of all, the stored proc accepts the XML passed to it as a string.

PROCEDURE dbo.XMLINFO
 @xmlString NVARCHAR(MAX)

Simply storing the accepted string into a variable with the new XML data-type converts it automatically into a true XML document that can be parsed:

DECLARE @xml XML
SET @xml = @xmlString

The top part of the XML that is being passed to this proc looks like this:

<doc>
<ver>1</ver>
<enter_date>2009-08-26T11:14:51</enter_date>
<bus>
<bus_id>1</bus_id>
<psw>password</psw>
</bus>
<user>
<id>abc1234</id>
</user>

At this point we can extract data from @xml by syntax such as:

select @user_id = @xml.value( '(/doc/user/id)[1]', 'varchar(30)')

(The @user_id variable will now contain the value 'abc1234'.)

I find that syntax a bit ugly, so I devised a way to take any XML passed in and populate a name/value pair table from it.

To get to the name/value pair, we make use of SQL Server's OpenXML command. This takes a handle from our xml and returns a result-set in which we can trace the hierarchy via the parentid and locate the actual values via the nodetype.

For instance, given a simple XML fragment such as:

<doc> 
 <name>  
  <first>John</first>
  <last>Doe</last>
 </name>
</doc>

OpenXML would return:

id parentid nodetype localname prefix namespaceuri datatype prev text 
 0     NULL        1 doc       NULL   NULL         NULL     NULL NULL 
 2        0        1 name      NULL   NULL         NULL     NULL NULL 
 3        2        1 first     NULL   NULL         NULL     NULL NULL
 5        3        3 #text     NULL   NULL         NULL     NULL John
 4        2        1 last      NULL   NULL         NULL        3 NULL
 6        4        3 #text     NULL   NULL         NULL     NULL Doe

The format of this result-set is known as an "edge table". In our process, we define a table variable that will capture this edge table format from the OpenXml. Then we populate it via an Insert/Select:

DECLARE @xmlEdgeTbl TABLE(
 id BIGINT, 
 parentid BIGINT, 
 node_type INT, 
 localname NVARCHAR(MAX),
 prefix NVARCHAR(MAX),
 namespaceuri nvarchar(MAX),
 datatype NVARCHAR(MAX),
 prev BIGINT,
 [text] NVARCHAR(MAX)
 )
DECLARE @hdl int
EXECUTE sp_xml_preparedocument @hdl OUTPUT, @xml
INSERT INTO @xmlEdgeTbl
 SELECT * FROM OPENXML(@hdl, N'/doc')
EXECUTE sp_xml_removedocument @hdl

Knowing our data, we use '/doc' as the root, but could have just used '' (an empty string) with the same results: you're telling it to start at the [unnamed] root. Then, we take the edge-table and populate a name/value pair table from it via a self-join from id to parentid:

DECLARE @nameValuePair TABLE(
 tagName NVARCHAR(100), 
 tagValue NVARCHAR(MAX), 
 tagId bigINT, 
 tagParentId bigint)
INSERT INTO @nameValuePair (
 tagName,
 tagValue,
 tagId,
 tagParentId
) 
SELECT 
 edge.localname, 
 txt.TEXT, 
 edge.id, 
 edge.parentid 
FROM @xmlEdgeTbl edge
JOIN @xmlEdgeTbl txt
ON edge.id = txt.parentid

Now, we can extract values from the name-value pair table with queries such as the following:

SELECT @bus_id = CONVERT(INT, tagValue) 
FROM @nameValuePair 
WHERE tagName = 'bus_id'
SELECT @psw = tagValue FROM @nameValuePair WHERE tagName = 'psw'

If we have recurring data (such as in our routing example) we can access it via the parent_tag column. So, if our XML contains something like this:

<entities>
 <entity client_id="3210">
   <name>Check</name>
   <desc>TOOL SUPPLY CO</desc>
   <created_by>abc1234</created_by>
 </entity>
 <entity client_id="1230">
   <name>Credit</name>
   <desc>RONS WAREHOUSE</desc>
   <created_by>4321</created_by>
 </entity>
</entities>

If we want to loop through every entity, processing the name and description, we can setup a cursor to return the id value for each entity tag, then loop through the cursor, using that id in Selects for particular tagNames where it matches the tagParentId:

DECLARE entityCursor CURSOR FOR 
 SELECT id FROM @xmlEdgeTbl 
 WHERE localname = 'entity' 
 ORDER BY id
OPEN entityCursor 
FETCH FROM entityCursor INTO @id
WHILE(@@FETCH_STATUS = 0)
BEGIN
 SELECT @name = tagValue 
 FROM @nameValuePair 
 WHERE tagParentId = @id AND tagName = 'name'
 SELECT @desc = tagValue 
 FROM @nameValuePair 
 WHERE tagParentId = @id AND tagName = 'desc'
 FETCH FROM entityCursor INTO @id
END

Returning XML from the proc

Here is the highly nested XML we want returned from the proc:

<doc>
 <status>A</status>
 <msg>
  <id>900004</id>
  <text>Request Accepted</text>
 </msg>
 <entities>
  <entity id="2222">
   <cat>9</cat>
   <client_id>123450</client_id>
   <entity_name>Sweep</entity_name>
   <entity_desc>PARTY OUTLET</entity_desc>
   <entity_date>2009-08-10T06:47:08.320</entity_date>
   <entity_creator>abc1234</entity_creator>
   <entity_ver>3333</entity_ver>
   <entity_amt>10.2700</entity_amt>
   <requests>
    <request id="44444">
     <req_date>2009-08-10T06:47:08.377</req_date>
     <req_amt>10.2700</req_amt>
     <req_priority>M</req_priority>
     <entr_by_id>abc1234</entr_by_id>
     <entr_by_name>first last</entr_by_name>
     <requestor_id>xyz1234</requestor_id>
     <requestor_name>First Last</requestor_name>
     <req_current_status>A</req_current_status>
     <req_current_status_date>2009-08-10T06:47:08.377
     </req_current_status_date>
     <responses>
      <response id="39474">
       <resp_date>2009-09-01T08:18:54.333</resp_date>
       <resp_user_id>zyxw4321</resp_user_id>
       <resp_name>First Last</resp_name>
       <resp_type>A</resp_type>
       <final_approver>Y</final_approver>
       <resp_status>A</resp_status>
       <delegator>
        <delegated_ind>N</delegated_ind>
       </delegator>
      </response>
     </responses>
    </request>
   </requests>
  </entity>
 </entities>
</doc>

There may be multiple entities, requests, and responses, however the <status> and <msg> sections must only occur once in the XML. Our process gathers all of the information needed into a denormalized temp table called #myInfo. The query that creates the XML from the temp table follows. We use FOR XML PATH to get the exact structure we want without having to resort to the complexity of FOR XML EXPLICIT. We find this method much more intuitive, and hence easier to maintain.

SELECT
 'A' 'status',
 MSG.id 'msg/id',
 MSG.TEXT 'msg/text',
 (
  SELECT entity_ver_id 'entity/@id',
  ENTITY_CAT_ID 'entity/cat',
  client_id 'entity/client_id',
  ver_name 'entity/entity_name',
  ver_desc 'entity/entity_desc',
  create_date 'entity/entity_date',
  created_by_user_pin 'entity/entity_creator',
  entity_ver_id 'entity/entity_ver',
  entity_amt 'entity/entity_amt',
  (
   SELECT 
   req_id 'request/@id',
   req_date 'request/req_date',
   amt 'request/req_amt',
   priority_code 'request/req_priority',
   Entr_by_pin 'request/entr_by_id',
   entr_by_name 'request/entr_by_name',
   Req_by_pin 'request/requestor_id',
   requestor_name 'request/requestor_name',
   [status] 'request/req_current_status',
   status_date 'request/req_current_status_date',
   req_cmnt 'request/req_cmnt',
   (
    SELECT
    resp_id 'response/@id',
    resp_date 'response/resp_date',
    Resp_user_pin 'response/resp_user_id',
    respondent_name 'response/resp_name',
    resp_type 'response/resp_type',
    final_approver 'response/final_approver',
    resp_status 'response/resp_status',
    (
     SELECT
     CASE WHEN RESP_USER_PIN IS NULL or
      COALESCE(resp_delegator_user_pin,RESP_USER_PIN) = RESP_USER_PIN 
       THEN 'N' 
       else 'Y' 
     end as 'delegated_ind',
     CASE WHEN RESP_USER_PIN IS NULL or
      COALESCE(resp_delegator_user_pin,RESP_USER_PIN)=RESP_USER_PIN
       THEN CAST(NULL AS VARCHAR(30))
       ELSE resp_delegator_user_pin 
     end as 'delegator_pin',
     CASE WHEN RESP_USER_PIN IS NULL or
      COALESCE(resp_delegator_user_pin,RESP_USER_PIN) = RESP_USER_PIN
       THEN CAST(NULL AS VARCHAR(30))
       ELSE Delegator_name
     end as 'delegator_name'
     FOR XML PATH(''), type 
    )'response/delegator'
    FOR XML PATH(''), type
   )'request/responses'
   FOR XML PATH(''), type
  ) 'entity/requests'
 FROM #myInfo
 FOR XML PATH(''), TYPE
) 'entities'
FROM (SELECT '900004' AS id, 'Request Accepted' AS TEXT)msg
FOR XML PATH(''), ROOT('doc'), type;

Notice how we use the column aliases to both name the resulting tag and to establish the hierarchy: 'response/@id' ensures that a parent <response> tag precedes the <id> tag. The "@" indicates to SQL Server that we want the value to be an attribute (instead of the default Element type). We add type to the FOR XML to prevent the angle-brackets in the result-set from being returned as &lt; and &gt;.

The nested selects (without FROM clauses) create the tag section structure and allow for recurring sections.

Note that as written above, the select will return an xml data-type. If you are running it from within SSMS you will see one hyperlinked value returned. Click on it and a new editor window will open with the xml displayed hierarchically.

In our case, we needed to pass this value back to our WebSphere application. Although the documentation states that an xml data-type is passed through the JDBC as a String to JAVA, we had problems when the string was long: it truncated it at 2,000 characters!

To get around that problem, we changed our proc to return an nvarchar(max) instead of the xml data-type. To accomplish this, we wrapped the above query as shown:

DECLARE @rslt NVARCHAR(MAX)
SELECT @rslt = CONVERT(NVARCHAR(MAX), x.rslt )
 FROM
 (
   ~complete query from above goes here~
 )X(rslt);
SELECT @rslt AS Xml_Result

Using these techniques allows for great flexibility in passing a variable number of "parameters" to a stored-procedure, and returning a variable amount of information in a single nvarchar or xml data-type.

Rate

3.13 (24)

You rated this post out of 5. Change rating

Share

Share

Rate

3.13 (24)

You rated this post out of 5. Change rating