sql for xml path

Question

Post reply

sql for xml path

changesky

SSC Veteran

Points: 232
More actions
July 29, 2011 at 11:10 am

#390088

Dear ALL,
I have a table named A,
CREATE TABLE [dbo].[A](
[Client] [varchar](25) NOT NULL,
[KHID] [int] NOT NULL,
[DHSLRID] [int] NOT NULL,
[BZ] [varchar](1000) NULL,
[FWCPDM] [varchar](40) NOT NULL,
[LDSJ] [datetime] NOT NULL,
[SH] [varchar](30) NOT NULL,
[GDDH] [varchar](40) NULL,
[LXR] [varchar](40) NOT NULL,
[DZ] [varchar](100) NOT NULL,
[YDDH] [varchar](40) NULL,
[LDR] [varchar](40) NULL,
[LDDH] [varchar](20) NULL,
[FWFDQR] [datetime] NULL,
[WTMS] [varchar](3000) NOT NULL,
[WTJD] [varchar](2000) NOT NULL,
[WTFL] [int] NOT NULL,
[SFJJ] [int] NOT NULL
)
GO
INSERT [dbo].[A] ([Client], [KHID], [DHSLRID], [BZ], [FWCPDM], [LDSJ], [SH], [GDDH], [LXR], [DZ], [YDDH], [LDR], [LDDH], [FWFDQR], [WTMS], [WTJD], [WTFL], [SFJJ]) VALUES (N'218', '4065479', '134519', N'TEL', N'729', '2011-07-30', N'2011', N'83641613-803', N'AD1', N'AD2', N'AD3', N'AD4', N'AD5', '2011-07-30', N'81A,%', N'BBB', '81', '1')
INSERT [dbo].[A] ([Client], [KHID], [DHSLRID], [BZ], [FWCPDM], [LDSJ], [SH], [GDDH], [LXR], [DZ], [YDDH], [LDR], [LDDH], [FWFDQR], [WTMS], [WTJD], [WTFL], [SFJJ]) VALUES (N'218', '4065479', '134520', N'TEL', N'729', '2011-07-30', N'2011', N'83641613-803', N'AD1', N'AD2', N'AD3', N'AD4', N'AD5', '2011-07-30', N'81A,%', N'BBB', '81', '1')
The problem is how to generate the xml document as the following format, using the sql for auto path for other skill.
<ResultSet>
<Master>
<RowMaster id="1">
<col name="KHID">4065479</col>
<col name="DHSLRID">134519</col>
<col name="BZ">TEL</col>
<col name="FWCPDM">729</col>
<col name="LDSJ">2011-01-03 08:46:44</col>
<col name="SH">2011</col>
<col name="GDDH">3641613-803</col>
<col name="LXR">AD1</col>
<col name="DZ">AD2</col>
<col name="YDDH">AD3</col>
<col name="LDR">AD4</col>
<col name="LDDH">AD5</col>
<col name="FWFDQR">2012-03-12</col>
</RowMaster>
</Master>
<DetailS>
<Detail NO="1" name="record" >
<RowDetail id="1">
<col name="WTMS">81A,%</col>
<col name="WTJD">BBB</col>
<col name="WTFL">81</col>
<col name="SFJJ">1</col>
</RowDetail>
</Detail>
</DetailS>
</ResultSet>
<ResultSet>
<Master>
<RowMaster id="1">
<col name="KHID">4065479</col>
<col name="DHSLRID">134520</col>
<col name="BZ">TEL</col>
<col name="FWCPDM">729</col>
<col name="LDSJ">2011-01-03 08:46:44</col>
<col name="SH">2011</col>
<col name="GDDH">3641613-803</col>
<col name="LXR">AD1</col>
<col name="DZ">AD2</col>
<col name="YDDH">AD3</col>
<col name="LDR">AD4</col>
<col name="LDDH">AD5</col>
<col name="FWFDQR">2012-03-12</col>
</RowMaster>
</Master>
<DetailS>
<Detail NO="1" name="record" >
<RowDetail id="1">
<col name="WTMS">81A,%</col>
<col name="WTJD">BBB</col>
<col name="WTFL">81</col>
<col name="SFJJ">1</col>
</RowDetail>
</Detail>
</DetailS>
</ResultSet>
Any help,Thanks very much:-)

Viewing 8 posts - 1 through 8 (of 8 total)

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

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 1

Some thing like this

SELECT * FROM [dbo].[A]

FOR XML PATH('RowMaster'), ROOT('Master')

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

changesky SSC Veteran Points: 232 More actions · Answer 2

Thanks,but cannot generate the specific xml format with one root segment ('ResultSet') and two child segment ('Master','DetailS') for every table record.

<ResultSet>

<Master>

<RowMaster id="1">

<col name="KHID">4065479</col>

<col name="DHSLRID">134519</col>

<col name="BZ">TEL</col>

<col name="FWCPDM">729</col>

<col name="LDSJ">2011-01-03 08:46:44</col>

<col name="SH">2011</col>

<col name="GDDH">3641613-803</col>

<col name="LXR">AD1</col>

<col name="DZ">AD2</col>

<col name="YDDH">AD3</col>

<col name="LDR">AD4</col>

<col name="LDDH">AD5</col>

<col name="FWFDQR">2012-03-12</col>

</RowMaster>

</Master>

<DetailS>

<Detail NO="1" name="record" >

<RowDetail id="1">

<col name="WTMS">81A,%</col>

<col name="WTJD">BBB</col>

<col name="WTFL">81</col>

<col name="SFJJ">1</col>

</RowDetail>

</Detail>

</DetailS>

</ResultSet>

LutzM SSC Guru Points: 107049 More actions · Answer 3

Here's one possible solution. In order to generate multiple nodfes with the same node name it is required to unpivot the original table first. Then two subqueries are used to build the Master and DetailS nodes and finally those two nodes are wrapped by the ResultSet node.

You'äll need to expand it to include all columns required.

As a side note: When unpivoting the columns, you'll need to convert all columns to the same data type. I also had to add a unique row number... I hope the original table will have such a row identifier... 😉

; WITH cte AS

(

SELECT client,id, colname, colvalue

FROM

(SELECT

ROW_NUMBER() OVER(ORDER BY client,[DHSLRID]) AS id,

client,

CAST([KHID] AS VARCHAR(3000)) AS [KHID],

CAST([DHSLRID] AS VARCHAR(3000)) AS [DHSLRID],

[WTMS],

CAST([WTJD]AS VARCHAR(3000)) AS [WTJD]

FROM a) p

UNPIVOT

(colvalue FOR colname IN

([KHID], [DHSLRID], [WTMS], [WTJD])

)AS unpvt

)

SELECT

id AS 'Master/RowMaster/@id',

(

SELECT

colname AS 'col/@name',

colvalue AS 'col'

FROM cte cte2

WHERE cte1.id=cte2.id AND colname IN ('KHID', 'DHSLRID')

FOR XML PATH(''),type

) AS 'Master/RowMaster',

'1' AS 'DetailS/Detail/@NO',

'record' AS 'DetailS/Detail/@name',

id AS 'DetailS/Detail/RowDetail/@id',

(

SELECT

colname AS 'col/@name',

colvalue AS 'col'

FROM cte cte2

WHERE cte1.id=cte2.id AND colname IN ('WTMS', 'WTJD')

FOR XML PATH(''),type

) AS 'DetailS/Detail/RowDetail'

FROM cte cte1

GROUP BY id

FOR XML PATH('ResultSet')

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]

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 4

I see the Client column from your table is missing in the XML data result set is that the intended to be like that.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

changesky SSC Veteran Points: 232 More actions · Answer 5

Thank you Lutz for providing a good solutioin with cte,unpivot and sunquey to build two nodes. According to your code, I finished the SP to generate to the whole xml format. 🙂

Here is one question, another way is using the C# or other language to query the table and write the xml document one by element by element. I know the SQL can build the xml by batch.

But which way have the more high performance?

Thanks again!

LutzM SSC Guru Points: 107049 More actions · Answer 6

changesky (8/3/2011)
Thank you Lutz for providing a good solutioin with cte,unpivot and sunquey to build two nodes. According to your code, I finished the SP to generate to the whole xml format. 🙂
Here is one question, another way is using the C# or other language to query the table and write the xml document one by element by element. I know the SQL can build the xml by batch.
But which way have the more high performance?
Thanks again!

Simple answer: it depends.

The easiest way to figure it out is test, test, and test. 😀

Your question is similar to "What is the better tool: a hammer or a screwdriver?"

Set up a test scenario with a few hundred thousand rows, writing the results to a file or table (definitely not as an output to the result window) and compare each solution in terms of duration, cpu and I/O.

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]

changesky SSC Veteran Points: 232 More actions · Answer 7

changesky

SSC Veteran

Points: 232

August 5, 2011 at 8:15 am

#1364377

THANK YOU!:-P

I WILL TEST IT.