SQL to XML grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL to XML grouping

Author

Topic

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-07-22 : 07:04:39

Please can somebody help?

WITH XMLNAMESPACES (DEFAULT 'http://www.google.com')
SELECT top 1
'A'as [Column1]
,'B'as [Column2]
,'C'as [Column3]
,'D'as [Column4]
,'E'as [Column5]
,'F'as [Column6]
,'G'as [Column7]
,'H'as [Column8]
FROM [Muj_test].[dbo].[XMLNEWWorking]
FOR XML RAW ('SRecord'), ROOT ('SOS')
, ELEMENTS XSINIL;

Result:-

<SOS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.google.com">
<SRecord>
<Column1>A</Column1>
<Column2>B</Column2>
<Column3>C</Column3>
<Column4>D</Column4>
<Column5>E</Column5>
<Column6>F</Column6>
<Column7>G</Column7>
<Column8>H</Column8>
</SRecord>
</SOS>

i would like the xml to inculde grouping like this:-

<SOS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.google.com">
<SRecord>
<PersonalXX>
<Column1>A</Column1>
<Per>
<Column2>B</Column2>
</Per>
<Column3>C</Column3>
</PersonalXX>
<Ref>
<Column4>D</Column4>
<Column5>E</Column5>
</Ref>
<Test>
<Column6>F</Column6>
<Column7>G</Column7>
<Column8>H</Column8>
</Test>
</SRecord>
</SOS>

How do i add these in the SQL?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-22 : 08:09:28

Use XML PATH like this.

WITH XMLNAMESPACES (DEFAULT 'http://www.google.com')
SELECT top 1
'A'as [PersonalXX/Column1]
,'B'as [PersonalXX/Per/Column2]
,'C'as [PersonalXX/Column3]
,'D'as [Ref/Column4]
,'E'as [Ref/Column5]
,'F'as [Test/Column6]
,'G'as [Test/Column7]
,'H'as [Test/Column8]
FROM [Muj_test].[dbo].[XMLNEWWorking]
FOR XML PATH('SRecord'), ROOT ('SOS')
, ELEMENTS XSINIL;

I assume you know that in the example you posted, you don't need the "FROM [Muj_test].[dbo].[XMLNEWWorking]".

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-07-22 : 08:29:23

yes i do know, i was just editing my query for posting but didn't see that at the time. yes thank you i will try this it seems to work in the exmaple you provide. thank you

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-07-22 : 11:17:30

Hi James,

Would you please help me with another issue?

I have an xml example which i need to replicate using t-sql.In the example the xmls format is like so:-

- <Referrals>
<column1="1" />
<column2="C2918341" />
<column3="RR807" />
<column4>2011-09-09</column4>
<column5>2011-09-10</column5>
</Referrals>

and the what i am producing is a bit different:-

<Referrals>
<column1>1</column1>
<column1>C2918341</column2>
<column1>RR807</column3>
<column1>2011-09-09</column4>
<column1>2011-09-10</column5>
</Referrals>

my xml is producing closing tags where as in the example there are single close tags aswell as speachmark("").

Thank you
again

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-22 : 11:34:21

quote:
- <Referrals>
<column1="1" />
<column2="C2918341" />
<column3="RR807" />
<column4>2011-09-09</column4>
<column5>2011-09-10</column5>
</Referrals>

This is not well-formed XML. So SQL XML cannot generate this. I suspect that this XML did not come from any XML generator, instead, someone manually typed it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources