Matt Miller (#4) (4/23/2014)
I was going to go down the route of a CTE as well, but the OP mentioned 2005.
My bad, then the option is to nest it
😎
DECLARE @XML XML = N'<applicant>
<summaryData>
<dataName>Age</dataName>
<dataValue>45</dataValue>
</summaryData>
<summaryData>
<dataName>LastName</dataName>
<dataValue>Jones</dataValue>
</summaryData>
<summaryData>
<dataName>NotThis</dataName>
<dataValue>Value</dataValue>
</summaryData>
</applicant>
<applicant>
<summaryData>
<dataName>Age</dataName>
<dataValue>48</dataValue>
</summaryData>
<summaryData>
<dataName>LastName</dataName>
<dataValue>Jones</dataValue>
</summaryData>
</applicant>';
DECLARE @APPLICANT TABLE
(
guid INT IDENTITY(1,1) NOT NULL
,LogXML XML NOT NULL
);
INSERT INTO @APPLICANT(LogXML)
SELECT @XML;
SELECT
*
FROM
(
SELECT
APEN.APPL_RID
,SUMMARY.DATA.value('./dataName[1]','NVARCHAR(250)') AS SD_dataName
,SUMMARY.DATA.value('./dataValue[1]','NVARCHAR(250)') AS SD_dataValue
FROM
(
SELECT
AP.guid
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS APPL_RID
,APPL.ICANT.query('(.)') AS ANCHOR
FROM @APPLICANT AP
OUTER APPLY AP.LogXML.nodes('applicant') AS APPL(ICANT)
) AS APEN
OUTER APPLY APEN.ANCHOR.nodes('applicant/summaryData') AS SUMMARY(DATA)
WHERE SUMMARY.DATA.value('./dataName[1]','NVARCHAR(250)') IN
(N'Age',N'LastName')
) AS APSU
PIVOT (MAX(SD_dataValue)
FOR SD_dataName IN ([Age],[LastName])) AS PTBL
;