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:
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:
<CustomerDetails><FirstName>John</FirstName></CustomerDetails>
Ok, this will get passed to stored procedure 2 as input param:
DECLARE @hdoc AS INT;
EXEC sp_xml_preparedocument
@hdoc OUTPUT
,@p_FirstNames
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.