March 19, 2010 at 12:42 pm
Hi,
I've been looking around trying to figure out how to retrieve my XML data into table form, and haven't had any luck. My situation is a little unique, though, and I'm not entirely sure what I want to do is possible:
I have a column in my table called auditXMLData, containing XML that looks like this:
<panel>
<name>coolingContent</name>
<question questionText="" ID="cooling_system_types" Value="central AC" />
.
.
.
<question questionText="" ID="cooling_payback" Value="2.00" />
</panel>
I want to translate this data into a table, where each <question> element maps to a column, so this:
auditIDauditXMLDataotherStuff
1<panel><name>coolingContent</name><question…blah
2<panel><name>coolingContent</name><question…blah
3<panel><name>coolingContent</name><question…blah
becomes this:
auditIDcooling_system_types …cooling_payback
1central AC…100
2window AC…250
3none…0
I've poked around the forums here, and the closest I've come so far is this one, which shows how to pull out each attribute as a column. I've tried to emulate it, as shown below:
SELECT
d.value('@ID','varchar(50)') as [ID]
,d.value('@Value','varchar(50)') as [Value]
FROM tblCustomerAudits_
CROSS APPLY auditXMLData.nodes('/question') as a(d)
But I can't seem to get any results from that, either, so I figure either I'm doing it wrong, or it works a little differently in 2k8 (the original forum thread was for 2k5)
Any thoughts? Is this even possible?
Thanks,
Jesse
March 19, 2010 at 3:15 pm
Here's what I came up with:
DECLARE @xml XML
DECLARE @strFieldName VARCHAR(200)
SET @strFieldName = 'txtFieldSubmitURLH'
SET @xml='
<panel>
<name>coolingContent</name>
<question questionText="" ID="cooling_system_types" Value="central AC" />
<question questionText="" ID="cooling_payback" Value="200" />
</panel>
<panel>
<name>coolingContent</name>
<question questionText="" ID="cooling_system_types" Value="window AC" />
<question questionText="" ID="cooling_payback" Value="250" />
</panel>
<panel>
<name>coolingContent</name>
<question questionText="" ID="cooling_system_types" Value="none" />
<question questionText="" ID="cooling_payback" Value="0" />
</panel>'
-- your original query
SELECT
d.value('@ID','varchar(50)') as [ID],
d.value('@Value','varchar(50)') as [Value]
FROM @xml.nodes('panel/question') as a(d)
-- a modified solution
; WITH cte AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) auditID,
Node
FROM
(SELECT X.Y.query('.') AS Node
FROM @XML.nodes('panel') X(Y)
) Z
),
cte2 AS
(
SELECT
auditID,
d.value('@ID','varchar(50)') AS [ID],
d.value('@Value','varchar(50)') AS [VALUE]
FROM cte
CROSS APPLY Node.nodes('panel/question') AS a(d)
)
SELECT
auditID,
MAX(CASE WHEN id='cooling_system_types' THEN VALUE ELSE '' END) AS cooling_system_types,
MAX(CASE WHEN id='cooling_payback' THEN VALUE ELSE '' END) AS cooling_payback
FROM cte2
GROUP BY auditID
/* result set
IDValue
cooling_system_typescentral AC
cooling_payback200
cooling_system_typeswindow AC
cooling_payback250
cooling_system_typesnone
cooling_payback0
auditIDcooling_system_typescooling_payback
1central AC200
2window AC250
3none0
*/
In your first query you missed the top node 'panel'. There is no difference between 2K5 and 2K8 regarding that specific function.
The question I had to guess the answer: what would define the final order of the elements?
March 22, 2010 at 9:36 am
Thank you, this is exactly what I needed.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply