Click here to Skip to main content
15,895,011 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I have 2 stored procedure worked by previous developers in my team. Stored procedure 1 is to retrieve records and casting them as XML format. Stored procedure 2 will take the output as its input param and use it to generate another records. The logic is straightforward and but one thing tedious is as follow:
SQL
SELECT   
	CAST 
	(   
		(
			SELECT DISTINCT
				c.FirstName
			FROM   
				CustomerDetail.Customer AS c
				 XML RAW('FirstName'), ROOT('CustomerDetails')  
   
		) AS VARCHAR(MAX)
	)


So, from the above SQL statement, I would have something like:
XML
<CustomerDetails><FirstName>John</FirstName></CustomerDetails>


Ok, this will get passed to stored procedure 2 as input param:
SQL
DECLARE @hdoc AS INT;  
EXEC sp_xml_preparedocument   
	 @hdoc OUTPUT  
	,@p_FirstNames --The above XML syntax
  
DECLARE @CustomerDetail TABLE 
(   
	FirstName VARCHAR(100)
)   
         
INSERT INTO 
@CustomerDetail 
SELECT   
*  
FROM   
OPENXML(@hdoc,'/ROOT/CustomerDetails/FirstName',1) WITH(FirstName VARCHAR(100))


Then I will simply SELECT * FROM @CustomerDetail and what i get is empty table without the firstname i passed.
While i know where the problem is(due to missing ROOT in my input param), but i cannot figure out why the application which is using the above 2 stored procedure is running correctly(all business logics) without any data mismatch/integration issue/etc at all in all these time.

What I have tried:

1. Add ROOT into the input param and it is able to generate records. In this case, John.
2. Remove the ROOT again, John "disappear".
3. Logics running now(has been for many years), is fine, data able to generate(without ROOT element).
4. Looked Microsoft website, all example shown has ROOT element.
Posted
Updated 4-Jun-18 17:33pm
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900