First of all: FROM OPENXML (together with the stored procedures to prepare and to remove a document) is outdated and should not be used anymore. Rather use the native XML methods provided by the XML data type.
Try it like this:
DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML =
'<offers>
<offer>
<a>AAA1</a>
<param name="B">A1B</param>
<param name="C">A1C</param>
</offer>
<offer>
<a>AAA2</a>
<param name="B">A2B</param>
<param name="C">A2C1</param>
<param name="C">A2C2</param>
</offer>
</offers>';
--The query
WITH cte AS
(
SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
,B.p.value('@name','nvarchar(100)') AS Param_Name
,A.o.query('.') TheOffer
FROM @XML.nodes('/offers/offer') A(o)
CROSS APPLY A.o.nodes('param') B(p)
)
SELECT Offer_a
,MAX(CASE WHEN Param_Name='B' THEN REPLACE(concatParams,' ',' / ') END) AS ParamB
,MAX(CASE WHEN Param_Name='C' THEN REPLACE(concatParams,' ',' / ') END) AS ParamC
FROM cte
CROSS APPLY(SELECT TheOffer.query('data(offer/param[@name=sql:column("Param_Name")]/text())').value('.','nvarchar(max)')) A(concatParams)
GROUP BY Offer_a;
The idea in short:
The cte will return a set of the text() of <a>, the value of param/@name and the XML-fragment <offer> for the corresponding <a>.
The magic happens in the CROSS APPLY(SELECT ...). This sub-select will fetch the params fitting to the current row's Param_Name in a row-wise call. The XQuery-function sql:column() allows to introduce a value of the current row into a XQuery-expression.
Very important is the XQuery-function data(). This will return all data in this path separated by a blank. Regrettfully this function does not allow for a user defined separator.
Therefore a big warning: If your param values include blanks, you will not know, where to separate them... If you need this, please come back with a comment.
The second piece of magic is the grouped aggregation. We group by Offer_a and use MAX() to allow the usage of non-grouping columns. This is an old-fashioned pivot approach...
The replace will put the slashes instead of the blanks (from data()).
UPDATE: If there are blanks in your param values...
Approach 1
We use XQuery/FLWOR to go through the nodes and do the concatenation within Xquery. We can use sql:variable() to introduce a declared variable into the XPath-expression:
DECLARE @delimiter VARCHAR(100)=' / ';
WITH cte AS
(
SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
,B.p.value('@name','nvarchar(100)') AS Param_Name
,A.o.query('.') TheOffer
FROM @XML.nodes('/offers/offer') A(o)
CROSS APPLY A.o.nodes('param') B(p)
)
SELECT Offer_a
,MAX(CASE WHEN Param_Name='B' THEN STUFF(concatParams,1,LEN(@delimiter),'') END) AS ParamB
,MAX(CASE WHEN Param_Name='C' THEN STUFF(concatParams,1,LEN(@delimiter),'') END) AS ParamC
FROM cte
CROSS APPLY(SELECT TheOffer.query('for $p in offer/param[@name=sql:column("Param_Name")]/text()
return <x>{concat(sql:variable("@delimiter"),$p)}</x>
').value('.','nvarchar(max)')) A(concatParams)
GROUP BY Offer_a;
...or Approach 2
We extract all values to an intermediate set and use a correlated sub query together with the FOR XML approach to get the concatenated parameters.
WITH cte AS
(
SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
,B.p.value('@name','nvarchar(100)') AS Param_Name
,A.o.query('.') TheOffer
FROM @XML.nodes('/offers/offer') A(o)
CROSS APPLY A.o.nodes('param') B(p)
)
,cte2 AS
(
SELECT cte.Offer_a
,cte.Param_Name
,A.relatedParams.value('text()[1]','nvarchar(100)') AS ParamValue
FROM cte
CROSS APPLY TheOffer.nodes('offer/param[@name=sql:column("Param_Name")]') A(relatedParams)
)
SELECT Offer_a
,MAX(CASE WHEN Param_Name='B' THEN concatParamValues END) AS paramB
,MAX(CASE WHEN Param_Name='C' THEN concatParamValues END) AS paramC
FROM cte2
CROSS APPLY(SELECT STUFF((SELECT DISTINCT CONCAT(@delimiter,ParamValue)
FROM cte2 csq
WHERE csq.Offer_a=cte2.Offer_a
AND csq.Param_Name=cte2.Param_Name
FOR XML PATH('')),1,LEN(@delimiter),'')) A(concatParamValues)
GROUP BY Offer_a;
... and if your XML might contain forbidden characters use this at the end
CROSS APPLY(SELECT STUFF((SELECT DISTINCT CONCAT(@delimiter,ParamValue)
FROM cte2 csq
WHERE csq.Offer_a=cte2.Offer_a
AND csq.Param_Name=cte2.Param_Name
FOR XML PATH(''),TYPE).value('.','nvarchar(100)'),1,LEN(@delimiter),'')) A(concatParamValues)
UPDATE 2
You placed an additional question in your comment, but I must admit, I did not really get what you need. If I get this correctly, you are having entities within your values. You did get your result in principles, but these entities remained untranslated? Correct?
In my answer above there is everything you need already. Just to make it clear, a fully working example:
DECLARE @delimiter VARCHAR(100)=' / ';
DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML =
'<offers>
<offer>
<a>AAA1</a>
<param name="B">A1B&</param> <!-- Some typical entities -->
<param name="C">A1C<</param>
</offer>
<offer>
<a>AAA2</a>
<param name="B">A2B after space</param> <!-- A case with some spaces -->
<param name="C">A2C1 A</param> <!-- The A is the capital letter A as entity-->
<param name="C">A2C2</param>
</offer>
</offers>';
WITH cte AS
(
SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
,B.p.value('@name','nvarchar(100)') AS Param_Name
,A.o.query('.') TheOffer
FROM @XML.nodes('/offers/offer') A(o)
CROSS APPLY A.o.nodes('param') B(p)
)
,cte2 AS
(
SELECT cte.Offer_a
,cte.Param_Name
,A.relatedParams.value('text()[1]','nvarchar(100)') AS ParamValue
FROM cte
CROSS APPLY TheOffer.nodes('offer/param[@name=sql:column("Param_Name")]') A(relatedParams)
)
SELECT Offer_a
,MAX(CASE WHEN Param_Name='B' THEN concatParamValues END) AS paramB
,MAX(CASE WHEN Param_Name='C' THEN concatParamValues END) AS paramC
FROM cte2
CROSS APPLY(SELECT STUFF((SELECT DISTINCT CONCAT(@delimiter,ParamValue)
FROM cte2 csq
WHERE csq.Offer_a=cte2.Offer_a
AND csq.Param_Name=cte2.Param_Name
FOR XML PATH(''),TYPE).value('.','nvarchar(100)'),1,LEN(@delimiter),'')) A(concatParamValues)
GROUP BY Offer_a;
The result
Offer_a paramB paramC
AAA1 A1B& A1C<
AAA2 A2B after space A2C2 / A2C1 A
The "approach 1" from above would work to the same result:
WITH cte AS
(
SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
,B.p.value('@name','nvarchar(100)') AS Param_Name
,A.o.query('.') TheOffer
FROM @XML.nodes('/offers/offer') A(o)
CROSS APPLY A.o.nodes('param') B(p)
)
SELECT Offer_a
,MAX(CASE WHEN Param_Name='B' THEN STUFF(concatParams,1,LEN(@delimiter),'') END) AS ParamB
,MAX(CASE WHEN Param_Name='C' THEN STUFF(concatParams,1,LEN(@delimiter),'') END) AS ParamC
FROM cte
CROSS APPLY(SELECT TheOffer.query('for $p in offer/param[@name=sql:column("Param_Name")]/text()
return <x>{concat(sql:variable("@delimiter"),$p)}</x>
').value('.','nvarchar(max)')) A(concatParams)
GROUP BY Offer_a;
Finally I hope, that this solves your issues...
