XML TO SQL Temp Table

[Kirbydog]

Hello,

I have been trying to parse an XML fragment into an SQL temp table so that I can use in my Crystal Report 10. I tried using OPENXML but cannot get the data to flow into the table. Here is an example of what I am trying to do...

DECLARE @intDoc INT
DECLARE @chvXMLDoc varchar(8000)
DECLARE @id INT 'Input Parameter


-- UW_XXXXXXX XML document
Set @chvXMLDoc = '
<root>
<UW_XXXXXXXX
xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xmlns:ns3="

http://schemas.xxxx.com/yyyyy/XXXXXResponse"

xmlns:ns2="

http://schemas.xxxx.com/yyyyy/XXXXXTypes"

xmlns:ns1="

http://schemas.xxxx.com/yyyyyy/XXXXXXXCommonTypes"

xmlns:ns0="

http://schemas.xxxxx.com/yyyyy/XXXXXXXXXResponse">

<ns0:XXXXXXXXX_Response>

--- XML Data goes here ----

<UW_XXXXXXXX

-- Load the XML document into memory
EXEC sp_xml_preparedocument @intDoc OUTPUT, @chvXMLDoc

-- SELECT statement using OPENXML rowset provider

SELECT *
FROM OPENXML (@intDoc, '/root/UW_XXXXXXXX/XXXXXXXXResponse/XXXXXXXX_Response/XXXXXXXResponse')
WITH (LRS_ID int '@LRS_ID',
XXXXXXXXXXX_Response varchar(20), XXXXXResponse varchar(50),
AllXXXXXXTotalIncomeProposed varchar(10))
--WHERE @id = 2 'Retrieve a single record with an XML fragment in a field'

-- Remove the XML document from memory
EXEC sp_xml_removedocument @intDoc

The result is that the fields display but the data does not parse into them. Please keep in mind that there are many fields and I have given a small example.

Any ideas of what I am doing wrong? Any help or suggestions on how to do this?

Kirbydog :-]