display 3 rows - 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

General SQL Server Forums

New to SQL Server Programming

display 3 rows

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-18 : 20:40:08

declare @table table (ID INT IDENTITY, name varchar(40), Attribute xml)
insert @table
select 'vac', '<AdFields>
<AdField Default="112608" Type="Odometer" />
</AdFields>
<UpSells>
<UpSell Type="Provincial" StartDate="01/13/12 3:02:17 PM" EndDate="01/20/12 3:02:17 PM" SiteID="2" />
</UpSells>' union all
select 'press', '<AdFields>
<AdField Default="112608" Type="Odometer" />
</AdFields>
<UpSells />'
select * from @table

SELECT ID, pref.value('@Type[1]', 'Varchar(30)') AS 'GroupName'
FROM @table
CROSS APPLY Attribute.nodes ('UpSells/UpSell') AS People(pref)

i want to display 3 records even though in the 2nd rown i have no attribute in the elements.

result:
ID NAME GROUPNAME
1 vac provincial
1 vac provincial2
2 press null

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-19 : 07:02:16

How should the second row with GroupName = 'provincial2' be computed? There are no nodes with value = 'provincial2' anywhere in the XML document.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-19 : 11:50:43

Sorry wrong xml.

declare @table table (ID INT IDENTITY, name varchar(40), Attribute xml)
insert @table
select 'vac', '<AdFields>
<AdField Default="112608" Type="Odometer" />
</AdFields>
<UpSells>
<UpSell Type="Provincial" StartDate="01/13/12 3:02:17 PM" EndDate="01/20/12 3:02:17 PM" SiteID="2" />
<UpSell Type="Provincial2" StartDate="01/13/12 3:02:17 PM" EndDate="01/20/12 3:02:17 PM" SiteID="2" />
</UpSells>' union all
select 'press', '<AdFields>
<AdField Default="112608" Type="Odometer" />
</AdFields>
<UpSells />'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-19 : 12:18:03

Use OUTER APPLY instead of CROSS APPLY

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources