Rebuilding XML - HELP!!

Question

Post reply

Rebuilding XML - HELP!!

Andy Hyslop

SSCrazy Eights

Points: 9198
More actions
May 13, 2014 at 6:10 am

#307306

Morning / Afternoon / Evening (depending on location)
I need to Re-build some XML and need some help from the big guns 😀
First Lets get some data ect together
Tables and data
CREATE TABLE #TAIC
(
ID INT
,TAID INT
,FID INT
,Pass INT
,Addr INT
,Forename INT
,Surname INT
,DOB INT
,Alert INT
,XMLOrder INT
)
INSERT INTO #TAIC ([Id],TAID,FID,[Pass],Addr,[Forename],[Surname],[DOB],[Alert],[XMLOrder])VALUES(905,100,133,0,0,0,0,0,0,1)
INSERT INTO #TAIC ([Id],TAID,FID,[Pass],Addr,[Forename],[Surname],[DOB],[Alert],[XMLOrder])VALUES(906,100,107,0,0,0,0,0,0,2)
INSERT INTO #TAIC ([Id],TAID,FID,[Pass],Addr,[Forename],[Surname],[DOB],[Alert],[XMLOrder])VALUES(907,100,101,0,0,0,0,0,0,3)
INSERT INTO #TAIC ([Id],TAID,FID,[Pass],Addr,[Forename],[Surname],[DOB],[Alert],[XMLOrder])VALUES(908,100,113,0,0,0,0,0,0,4)
INSERT INTO #TAIC ([Id],TAID,FID,[Pass],Addr,[Forename],[Surname],[DOB],[Alert],[XMLOrder])VALUES(909,100,104,0,0,0,0,0,0,5)
INSERT INTO #TAIC ([Id],TAID,FID,[Pass],Addr,[Forename],[Surname],[DOB],[Alert],[XMLOrder])VALUES(910,100,106,0,0,0,0,0,0,6)
INSERT INTO #TAIC ([Id],TAID,FID,[Pass],Addr,[Forename],[Surname],[DOB],[Alert],[XMLOrder])VALUES(911,100,110,0,0,0,0,0,0,7)
INSERT INTO #TAIC ([Id],TAID,FID,[Pass],Addr,[Forename],[Surname],[DOB],[Alert],[XMLOrder])VALUES(912,100,118,0,0,0,0,0,0,8)
INSERT INTO #TAIC ([Id],TAID,FID,[Pass],Addr,[Forename],[Surname],[DOB],[Alert],[XMLOrder])VALUES(913,100,103,0,0,0,0,0,0,9)
INSERT INTO #TAIC ([Id],TAID,FID,[Pass],Addr,[Forename],[Surname],[DOB],[Alert],[XMLOrder])VALUES(914,100,123,0,0,0,0,0,0,10)
--SELECT *
--FROM
--#TAIC
--DROP TABLE #TAIC
CREATE TABLE #TAICR
(
ID INT
,TAICID INT
,RCode INT
,TICRDID INT
,XMLOrder INT
,RCodes VARCHAR(20)
)
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6316,905,103,7382,1,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6317,905,107,7386,2,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6318,905,109,7387,3,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6319,905,1001,7388,1,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6320,906,104,6911,1,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6321,906,106,6913,2,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6322,906,201,6917,3,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6323,906,202,6918,4,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6324,906,203,6919,5,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6325,906,204,6920,6,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6326,906,101,6908,7,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6327,906,108,6915,8,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6328,906,107,6914,9,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6329,907,101,6544,1,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6330,907,151,6558,2,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6331,907,161,6562,3,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6332,907,112,6549,4,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6333,907,113,6550,5,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6334,907,114,6551,6,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6335,907,1001,6582,1,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6336,907,1011,6586,2,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6337,907,1021,6590,3,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6338,908,101,6986,1,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6339,908,151,7000,2,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6340,908,161,7004,3,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6341,908,112,6991,4,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6342,908,113,6992,5,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6343,908,114,6993,6,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6344,908,1001,7024,1,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6345,908,1011,7028,2,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6346,908,1021,7032,3,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6347,909,101,6705,1,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6348,909,102,6706,2,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6349,909,120,6711,3,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6350,909,112,6708,4,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6351,909,113,6709,5,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6352,909,114,6710,6,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6353,909,1001,6717,1,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6354,910,111,6767,1,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6355,910,112,6768,2,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6356,910,102,6763,3,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6357,910,103,6764,4,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6358,910,104,6765,5,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6359,910,1001,6775,1,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6360,910,4151,6851,1,'Warning')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6361,911,101,6946,1,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6362,911,112,6948,2,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6363,911,113,6949,3,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6364,911,114,6950,4,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6365,911,1071,6961,1,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6366,912,112,7245,1,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6367,912,113,7246,2,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6368,912,115,7247,3,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6369,912,210,7248,4,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6370,912,211,7249,5,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6371,912,220,7250,6,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6372,912,230,7251,7,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6373,912,240,7253,8,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6374,912,250,7254,9,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6375,912,260,7256,10,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6376,912,261,7257,11,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6377,912,270,7258,12,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6378,913,120,6672,1,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6379,913,220,6673,2,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6380,913,221,6674,3,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6381,913,222,6675,4,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6382,913,240,6676,5,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6383,913,250,6677,6,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6384,913,251,6678,7,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6385,913,260,6679,8,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6386,914,102,7361,1,'Comment')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6387,914,1001,7366,1,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6388,914,1011,7367,2,'Match')
INSERT INTO #TAICR ([Id],TAICID,RCode,TICRDID,[XMLOrder],RCodes)VALUES(6389,914,1021,7368,3,'Match')
--select *
--from
--#TAICR
--drop table #TAICR
The joins for the above tables are:
select *
from
#TAIC AS TAIC
INNER JOIN #TAICR AS TAICR
ON TAIC.ID = TAICR.TAICID
Now what I need to do is build some XML from this data in the following EXACT Format (I've added comments to explain where bits come from) this also includes the order of the XML document the XMLOrder column should be used for the ORDER BY
<Array xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<RCodes>
<Comment>
<RCode>
<Description />
<Code>103</Code> 
<Override />
</RCode>
<RCode>
<Description />
<Code>107</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>109</Code>
<Override />
</RCode>
</Comment>
<Match>
<RCode>
<Description />
<Code>1001</Code>
<Override />
</RCode>
</Match>
<ID>133</ID> 
<Pass>NA</Pass> 
<Addr>NA</Addr> 
<Forename>NA</Forename> 
<Surname>NA</Surname> 
<DOB>NA</DOB> 
<Alert>NA</Alert> 
</RCodes>
<RCodes>
<Comment>
<RCode>
<Description />
<Code>104</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>106</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>201</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>202</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>203</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>204</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>101</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>108</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>107</Code>
<Override />
</RCode>
</Comment>
<ID>107</ID>
<Pass>NA</Pass>
<Addr>NA</Addr>
<Forename>NA</Forename>
<Surname>NA</Surname>
<DOB>NA</DOB>
<Alert>NA</Alert>
</RCodes>
<RCodes>
<Comment>
<RCode>
<Description />
<Code>101</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>151</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>161</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>112</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>113</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>114</Code>
<Override />
</RCode>
</Comment>
<Match>
<RCode>
<Description />
<Code>1001</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>1011</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>1021</Code>
<Override />
</RCode>
</Match>
<ID>101</ID>
<Pass>NA</Pass>
<Addr>NA</Addr>
<Forename>NA</Forename>
<Surname>NA</Surname>
<DOB>NA</DOB>
<Alert>NA</Alert>
</RCodes>
<RCodes>
<Comment>
<RCode>
<Description />
<Code>101</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>151</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>161</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>112</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>113</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>114</Code>
<Override />
</RCode>
</Comment>
<Match>
<RCode>
<Description />
<Code>1001</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>1011</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>1021</Code>
<Override />
</RCode>
</Match>
<ID>113</ID>
<Pass>NA</Pass>
<Addr>NA</Addr>
<Forename>NA</Forename>
<Surname>NA</Surname>
<DOB>NA</DOB>
<Alert>NA</Alert>
</RCodes>
<RCodes>
<Comment>
<RCode>
<Description />
<Code>101</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>102</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>120</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>112</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>113</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>114</Code>
<Override />
</RCode>
</Comment>
<Match>
<RCode>
<Description />
<Code>1001</Code>
<Override />
</RCode>
</Match>
<ID>104</ID>
<Pass>NA</Pass>
<Addr>NA</Addr>
<Forename>NA</Forename>
<Surname>NA</Surname>
<DOB>NA</DOB>
<Alert>NA</Alert>
</RCodes>
<RCodes>
<Comment>
<RCode>
<Description />
<Code>111</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>112</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>102</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>103</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>104</Code>
<Override />
</RCode>
</Comment>
<Match>
<RCode>
<Description />
<Code>1001</Code>
<Override />
</RCode>
</Match>
<Warning>
<RCode>
<Description />
<Code>4151</Code>
<Override />
</RCode>
</Warning>
<ID>106</ID>
<Pass>NA</Pass>
<Addr>NA</Addr>
<Forename>NA</Forename>
<Surname>NA</Surname>
<DOB>NA</DOB>
<Alert>NA</Alert>
</RCodes>
<RCodes>
<Comment>
<RCode>
<Description />
<Code>101</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>112</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>113</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>114</Code>
<Override />
</RCode>
</Comment>
<Match>
<RCode>
<Description />
<Code>1071</Code>
<Override />
</RCode>
</Match>
<ID>110</ID>
<Pass>NA</Pass>
<Addr>NA</Addr>
<Forename>NA</Forename>
<Surname>NA</Surname>
<DOB>NA</DOB>
<Alert>NA</Alert>
</RCodes>
<RCodes>
<Comment>
<RCode>
<Description />
<Code>112</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>113</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>115</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>210</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>211</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>220</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>230</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>240</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>250</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>260</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>261</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>270</Code>
<Override />
</RCode>
</Comment>
<ID>118</ID>
<Pass>NA</Pass>
<Addr>NA</Addr>
<Forename>NA</Forename>
<Surname>NA</Surname>
<DOB>NA</DOB>
<Alert>NA</Alert>
</RCodes>
<RCodes>
<Comment>
<RCode>
<Description />
<Code>120</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>220</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>221</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>222</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>240</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>250</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>251</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>260</Code>
<Override />
</RCode>
</Comment>
<ID>103</ID>
<Pass>NA</Pass>
<Addr>NA</Addr>
<Forename>NA</Forename>
<Surname>NA</Surname>
<DOB>NA</DOB>
<Alert>NA</Alert>
</RCodes>
<RCodes>
<Comment>
<RCode>
<Description />
<Code>102</Code>
<Override />
</RCode>
</Comment>
<Match>
<RCode>
<Description />
<Code>1001</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>1011</Code>
<Override />
</RCode>
<RCode>
<Description />
<Code>1021</Code>
<Override />
</RCode>
</Match>
<ID>123</ID>
<Pass>NA</Pass>
<Addr>NA</Addr>
<Forename>NA</Forename>
<Surname>NA</Surname>
<DOB>NA</DOB>
<Alert>NA</Alert>
</RCodes>
</Array>
Any help would be appreciated as struggling to solve this one :crazy:
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Here's something to get you started:

SELECT

CAST(

(SELECT '' AS [Description], RCode, '' as Override

FROM #TAICR ICR

WHERE ICR.TAICID=IC.ID AND RCodes = 'Comment'

ORDER BY [XMLOrder]

FOR XML PATH('RCode')

)

AS XML )Comment,

CAST(

(SELECT '' AS [Description], RCode, '' as Override

FROM #TAICR ICR

WHERE ICR.TAICID=IC.ID AND RCodes = 'Match'

ORDER BY [XMLOrder]

FOR XML PATH('RCode')

)

AS XML )Match,

ID,

CASE WHEN Pass = 0 THEN 'NA' ELSE CAST(Pass AS VARCHAR(30)) End as Pass,

Addr,

Forename,

Surname,

DOB,

Alert

FROM #TAIC IC ORDER BY ID FOR XML PATH ('RCodes'), ROOT('Array')

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Hunchback SSCrazy Points: 2649 More actions · Answer 2

Try:

--WITH XMLNAMESPACES (

--'http://www.w3.org/2001/XMLSchema-instance' AS xsi,

--'http://www.w3.org/2001/XMLSchema' AS xsd

--)

SELECT

(

SELECT

'' AS [Description],

T2.RCode AS RCode,

'' AS [Override]

FROM

#TAICR AS T2

WHERE

T2.TAICID = T1.ID

AND RCodes = 'Comment'

ORDER BY

XMLOrder

FOR XML PATH('RCode'), TYPE

) AS Comment,

(

SELECT

'' AS [Description],

T2.RCode AS RCode,

'' AS [Override]

FROM

#TAICR AS T2

WHERE

T2.TAICID = T1.ID

AND RCodes = 'Match'

ORDER BY

XMLOrder

FOR XML PATH('RCode'), TYPE

) AS Match,

T1.FID AS ID,

ISNULL(NULLIF(LTRIM(T1.Pass), 0), 'NA') AS Pass,

ISNULL(NULLIF(LTRIM(T1.Addr), 0), 'NA') AS Addr,

ISNULL(NULLIF(LTRIM(T1.Forename), 0), 'NA') AS Forename,

ISNULL(NULLIF(LTRIM(T1.Surname), 0), 'NA') AS Surname,

ISNULL(NULLIF(LTRIM(T1.DOB), 0), 'NA') AS DOB,

ISNULL(NULLIF(LTRIM(T1.Alert), 0), 'NA') AS Alert

FROM

#TAIC AS T1

ORDER BY

T1.XMLOrder

FOR XML PATH('RCodes'), ROOT('Array'), TYPE;

As you can see I am failing to provide an easy way to include the namespaces.

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

Both thank you for your time very helpful indeed , I was going with XML Explicit - am now on the right track

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe