July 9, 2014 at 12:54 pm
SQL 2012R2 Std
I have a sql statement to retrieve data in XML format. I have to use EXPLICIT because I have to use tag <CDATA>
I would like to store the XML returned into 1 column containing the XML tags.
For example:
#tmpXML
row 1: <DOC><NET_INFO net_ID=34><NOTES><![CDATA[description]]></NOTES></NET_INFO></DOC>
row 2 <DOC><NET_INFO net_ID=35><NOTES><![CDATA[description2]]></NOTES></NET_INFO></DOC>
select *
into #tmpXML
FROM (SELECT 1 AS Tag
, NULL AS Parent
, NULL AS 'DOC!1!'
, NULL AS 'NET_INFO!2!net_ID'
, NULL AS 'NOTES!3!CDATA'
UNION ALL
SELECT
2 AS Tag
, 1 AS Parent
, NULL
, net_ID
, NULL
FROM dbo.myTable
WHERE cust_id = 1
UNION ALL
SELECT
3 AS Tag
, 2 as Parent
, NULL
, NULL
, notes_CDATA
FROM dbo.myTable
WHERE cust_id = 1
FOR XML EXPLICIT )
But I keep getting an error message:
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
I have tried a couple of other ways but continue to get an error message.
July 10, 2014 at 9:55 am
Try this:
DECLARE @myTable AS TABLE (
cust_id int PRIMARY KEY CLUSTERED,
net_id int,
notes_CDATA varchar(20)
)
INSERT INTO @myTable VALUES(1, 1, 'description')
DECLARE @XF AS XML
SELECT @XF = (
SELECT *
FROM (SELECT 1 AS Tag
, NULL AS Parent
, NULL AS 'DOC!1!'
, NULL AS 'NET_INFO!2!net_ID'
, NULL AS 'NOTES!3!CDATA'
UNION ALL
SELECT
2 AS Tag
, 1 AS Parent
, NULL
, net_ID
, NULL
FROM @myTable
WHERE cust_id = 1
UNION ALL
SELECT
3 AS Tag
, 2 as Parent
, NULL
, NULL
, notes_CDATA
FROM @myTable
WHERE cust_id = 1
) X
FOR XML EXPLICIT
)
SELECT @XF AS XML_VALUE
INTO #tempXML
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2014 at 12:24 pm
That worked!
Thank you!!!!
July 10, 2014 at 1:23 pm
Glad I could help, and thanks for the update.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply