XML Format

Question

XML Format

SatishAyyar

SSCrazy

Points: 2825
More actions
October 11, 2010 at 10:55 am

#100321

Can anyone please help me in getting this format. Its really urgent. I really appreicate it. Thanks
Table Temp
Col1 Col2
10 20
30 40
Thanks,
<Records>
<Record>
<Prop Name = "Col1"><Pval>10</Pval></Prop>
<Prop Name = "Col2"><Pval>20</Pval></Pro>
</Record>
<Record>
<Prop Name = "Col1"><Pval>30</Pval></Prop>
<Prop Name = "Col2"><Pval>40</Pval></Prop>
</Record>
</Records>

Viewing 10 posts - 1 through 9 (of 9 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I don't see where PVal and such are defined in your table.

Beyond that, are you trying to use "For XML" to produce this from an SQL table, or are you using SQL 2000, or will you be doing this through some means other than T-SQL? You've given too little data to be able to really help much on this.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SatishAyyar SSCrazy Points: 2825 More actions · Answer 2

SatishAyyar

SSCrazy

Points: 2825

October 11, 2010 at 11:08 am

#1235139

<Pval> need to hard coded.

SatishAyyar SSCrazy Points: 2825 More actions · Answer 3

I need to write a sql query using for xml and get the data in this format

GSquared SSC Guru Points: 260824 More actions · Answer 4

What have you tried so far?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SatishAyyar SSCrazy Points: 2825 More actions · Answer 5

SELECT (SELECT top 2 ProductID,EComProductID FROM Products FOR XML AUTO, TYPE).query(

'<Record>{

for $c in /Products

return

<Pval >{data($c/@ProductID)}</Pval>

</Prop>,

for $c in /Products

return

<Pval >{data($c/@EComProductID)}</Pval>

</Prop>

}</Record>')

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 6

If you can UNPIVOT your data into something like the following:

DECLARE @Unpivot TABLE (RowId int, ColName varchar(50), ColVal int)

INSERT INTO @Unpivot(RowId, ColName, ColVal)

SELECT 1, 'Col1', 10 UNION ALL

SELECT 1, 'Col2', 20 UNION ALL

SELECT 2, 'Col1', 30 UNION ALL

SELECT 2, 'Col2', 40

then your required XML is quite easy to generate using "FOR XML PATH"

SELECT

(

SELECT XIN.ColName AS [@Name], XIN.ColVal AS [Pval]

FROM @Unpivot XIN

WHERE (XIN.RowId = XOUT.RowId)

ORDER BY XIN.ColName

FOR XML PATH('Prop'), Type

) AS Record

FROM @Unpivot XOUT

GROUP BY XOUT.RowId

ORDER BY XOUT.RowId

FOR XML PATH(''), ROOT('Records')

SatishAyyar SSCrazy Points: 2825 More actions · Answer 7

Thank You so much......I am working on it to submit to my manager.

Thanks once again

SatishAyyar SSCrazy Points: 2825 More actions · Answer 8

I used this sql script and generated the xml file through ssis script task and execute sql task. But I am not getting the version code on the top of the xml file. what should I do to get this. the code I used in script task is as follow.

Dim sw As New IO.StreamWriter(Dts.Variables("User::FilePath").Value.ToString() + Dts.Variables("User::FileName").Value.ToString() + "_1.XML")

sw.Write(Dts.Variables("User::Content").Value.ToString())

sw.Dispose()

'

Dts.TaskResult = ScriptResults.Success

SatishAyyar SSCrazy Points: 2825 More actions · Answer 9

Can anyone please help me in having the version tag line on the top of the xml file generated by script I provide.

Thanks in advance