XML Query help

[ptheriault]

Hey guys,
I have the following TSQL.

DECLARE @docXML XML

SET @docXML = (SELECT '1' AS account_level
       ,'FirstComp' AS account_number
       ,'' AS parent_account_number
       ,'FirstComp Insurance' AS account_name
       ,'' AS policy_number
       ,CONVERT(Varchar(1),'W') AS coverage_type
       ,'' AS effective_date
       ,'' AS termination_date
       ,'' AS address_1
       ,'' as city
       ,'' as state
       ,'' as zip
FROM policy_InsuredClaims_IC Record
UNION
SELECT '2' AS account_level
       ,CONVERT(Varchar(25),Record.PP_PaperID) AS account_number
       ,'FirstComp' AS parent_account_number
       ,PI.PI_Paper AS account_name
       ,'' AS policy_number
       ,CONVERT(Varchar(1),'W') AS coverage_type
       ,'' AS effective_date
       ,'' AS termination_date
       ,PI.PI_Address AS address_1
       ,PI.PI_City AS city
       ,PI.PI_State AS state
       ,PI.PI_PostalCode as zip
FROM dbo.Partner_Paper Record
       join pricing_PaperInformation_PI PI ON Record.PP_PaperID = PI.PI_ID
UNION
SELECT '3' AS account_level
       ,CONVERT(Varchar(25),Record.IPI_ID) AS account_number
       ,CONVERT(Varchar(25),PP.PP_PaperID) AS parent_account_number
       ,II.II_InsuredName AS account_name
       ,Record.IPI_PolicyNumber AS policy_number
       ,'W' AS coverage_type
       ,Record.IPI_EffectiveDate AS effective_date
       ,Record.IPI_ExpirationDate AS termination_date
       ,'' AS address_1
       ,'' as city
       ,'' as state
       ,'' as zip
FROM policy_InsuredPricingInfo_IPI Record
       JOIN Partner_Paper PP ON Record.IPI_PaperID = PP.PP_PaperID
       JOIN policy_InsuredInfo_II II ON Record.IPI_II_ID = II.II_ID
       JOIN policy_reference_Status_ST ST ON Record.IPI_ST_ID = ST.ST_ID
WHERE ST.ST_BrowsePolicies = 1
FOR XML AUTO, ROOT('Account'),ELEMENTS )
 
SELECT @docXML

However,
It produces this error.

Msg 1086, Level 15, State 1, Line 48
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.

Does anyone know what it means by this "wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it."



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[monksnake]

This is a stab here Paul but can you do this??:

WHERE ST.ST_BrowsePolicies = 1)
 
SELECT @docXML FOR XML AUTO, ROOT('Account'),ELEMENTS

<.

 

[gmmastros]

try...

[COLOR=blue]DECLARE[/color] @docXML XML

[COLOR=blue]SET[/color] @docXML = (

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] (
[COLOR=blue]SELECT[/color] [COLOR=red]'1'[/color] [COLOR=blue]AS[/color] account_level
       ,[COLOR=red]'FirstComp'[/color] [COLOR=blue]AS[/color] account_number
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] parent_account_number
       ,[COLOR=red]'FirstComp Insurance'[/color] [COLOR=blue]AS[/color] account_name
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] policy_number
       ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]Varchar[/color](1),[COLOR=red]'W'[/color]) [COLOR=blue]AS[/color] coverage_type
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] effective_date
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] termination_date
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] address_1
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] city
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] state
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] zip
[COLOR=blue]FROM[/color] policy_InsuredClaims_IC [COLOR=#FF00FF]Record[/color]
UNION
[COLOR=blue]SELECT[/color] [COLOR=red]'2'[/color] [COLOR=blue]AS[/color] account_level
       ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]Varchar[/color](25),[COLOR=#FF00FF]Record[/color].PP_PaperID) [COLOR=blue]AS[/color] account_number
       ,[COLOR=red]'FirstComp'[/color] [COLOR=blue]AS[/color] parent_account_number
       ,[COLOR=#FF00FF]PI[/color].PI_Paper [COLOR=blue]AS[/color] account_name
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] policy_number
       ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]Varchar[/color](1),[COLOR=red]'W'[/color]) [COLOR=blue]AS[/color] coverage_type
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] effective_date
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] termination_date
       ,[COLOR=#FF00FF]PI[/color].PI_Address [COLOR=blue]AS[/color] address_1
       ,[COLOR=#FF00FF]PI[/color].PI_City [COLOR=blue]AS[/color] city
       ,[COLOR=#FF00FF]PI[/color].PI_State [COLOR=blue]AS[/color] state
       ,[COLOR=#FF00FF]PI[/color].PI_PostalCode [COLOR=blue]as[/color] zip
[COLOR=blue]FROM[/color] dbo.Partner_Paper [COLOR=#FF00FF]Record[/color]
       [COLOR=blue]join[/color] pricing_PaperInformation_PI [COLOR=#FF00FF]PI[/color] [COLOR=blue]ON[/color] [COLOR=#FF00FF]Record[/color].PP_PaperID = [COLOR=#FF00FF]PI[/color].PI_ID
UNION
[COLOR=blue]SELECT[/color] [COLOR=red]'3'[/color] [COLOR=blue]AS[/color] account_level
       ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]Varchar[/color](25),[COLOR=#FF00FF]Record[/color].IPI_ID) [COLOR=blue]AS[/color] account_number
       ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]Varchar[/color](25),PP.PP_PaperID) [COLOR=blue]AS[/color] parent_account_number
       ,II.II_InsuredName [COLOR=blue]AS[/color] account_name
       ,[COLOR=#FF00FF]Record[/color].IPI_PolicyNumber [COLOR=blue]AS[/color] policy_number
       ,[COLOR=red]'W'[/color] [COLOR=blue]AS[/color] coverage_type
       ,[COLOR=#FF00FF]Record[/color].IPI_EffectiveDate [COLOR=blue]AS[/color] effective_date
       ,[COLOR=#FF00FF]Record[/color].IPI_ExpirationDate [COLOR=blue]AS[/color] termination_date
       ,[COLOR=red]''[/color] [COLOR=blue]AS[/color] address_1
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] city
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] state
       ,[COLOR=red]''[/color] [COLOR=blue]as[/color] zip
[COLOR=blue]FROM[/color] policy_InsuredPricingInfo_IPI [COLOR=#FF00FF]Record[/color]
       [COLOR=blue]JOIN[/color] Partner_Paper PP [COLOR=blue]ON[/color] [COLOR=#FF00FF]Record[/color].IPI_PaperID = PP.PP_PaperID
       [COLOR=blue]JOIN[/color] policy_InsuredInfo_II II [COLOR=blue]ON[/color] [COLOR=#FF00FF]Record[/color].IPI_II_ID = II.II_ID
       [COLOR=blue]JOIN[/color] policy_reference_Status_ST ST [COLOR=blue]ON[/color] [COLOR=#FF00FF]Record[/color].IPI_ST_ID = ST.ST_ID
[COLOR=blue]WHERE[/color] ST.ST_BrowsePolicies = 1
) [COLOR=blue]As[/color] A
[COLOR=blue]FOR[/color] XML AUTO, ROOT([COLOR=red]'Account'[/color]),ELEMENTS )
 
[COLOR=blue]SELECT[/color] @docXML

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Thanks George and MonkSnake.

Monksnake,
That didn't work. I think it's because the datatype of docXML is XML and I didn't specify the select as XML.

George,
That did the trick!

Thanks





- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Shee

http://www...

I was just guessing.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Good Guess!
<.

- Paul

- If at first you don't succeed, find out if the loser gets anything.