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 < and >.
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.