Xml output using for xml path - 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 2005 Forums

Transact-SQL (2005)

Xml output using for xml path

Author

Topic

csri
Starting Member

45 Posts

Posted - 2008-08-06 : 23:23:34

Dear All
I have two tables Roles(MnemId,RoleID) and Ip(Ipaddress,MnemId).

Roles Table

MnemId RoleId
1 1
2 2
3 3
4 3
NULL NULL

Ip Table
Ipaddress MnemId
10.10.10.1 1
10.10.10.2 2
10.10.10.3 3
10.10.10.4 4
null 5
null 6

I have to generate output like the below one

Required Output:
<terminal>
<ipaddress>10.10.10.1</ipaddress>
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
<Transactiongroup>3</Transactiongroup>
<Transactiongroup>4</Transactiongroup>
</Transactionpermission>
<ipaddress>10.10.10.2</ipaddress>
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
<Transactiongroup>3</Transactiongroup>
<Transactiongroup>4</Transactiongroup>
</Transactionpermission>
<ipaddress>10.10.10.3</ipaddress>
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
<Transactiongroup>3</Transactiongroup>
<Transactiongroup>4</Transactiongroup>
</Transactionpermission>
<ipaddress>10.10.10.4</ipaddress>
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
<Transactiongroup>3</Transactiongroup>
<Transactiongroup>4</Transactiongroup>
</Transactionpermission>
</terminal>

My select statement is

select ipaddress,
(select case when ipaddress is null then null else r.mnemid end as 'Transactiongroup'
from ip i,roles r where i.mnemid=r.mnemid for xml path(''),root('Transactionpermission'),type)
from ip for xml path(''),root('terminal')

output of above select statement

<terminal>
<ipaddress>10.10.10.1</ipaddress>
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
<Transactiongroup>3</Transactiongroup>
<Transactiongroup>4</Transactiongroup>
</Transactionpermission>
<ipaddress>10.10.10.2</ipaddress>
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
<Transactiongroup>3</Transactiongroup>
<Transactiongroup>4</Transactiongroup>
</Transactionpermission>
<ipaddress>10.10.10.3</ipaddress>
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
<Transactiongroup>3</Transactiongroup>
<Transactiongroup>4</Transactiongroup>
</Transactionpermission>
<ipaddress>10.10.10.4</ipaddress>
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
<Transactiongroup>3</Transactiongroup>
<Transactiongroup>4</Transactiongroup>
</Transactionpermission>
<ipaddress>null</ipaddress>
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
<Transactiongroup>3</Transactiongroup>
<Transactiongroup>4</Transactiongroup>
</Transactionpermission>
<ipaddress>null</ipaddress>
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
<Transactiongroup>3</Transactiongroup>
<Transactiongroup>4</Transactiongroup>
</Transactionpermission>
</terminal>

If ipaddress is null then
<Transactionpermission>
<Transactiongroup>1</Transactiongroup>
<Transactiongroup>2</Transactiongroup>
</Transactionpermission>
must not be displayed.

Can any one please tell me how to modify the above query so that I get the required output.urgent.please reply

Thanks
csri

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-07 : 01:12:06

(select r.mnemid as 'Transactiongroup'
from ip i,roles r where i.mnemid=r.mnemid for xml path(''),root('Transactionpermission'),type)
from ip for xml path(''),root('terminal')
where ipaddress is not null
?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources