Using CROSS APPLY - 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)

Using CROSS APPLY

Author

Topic

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-11-02 : 05:05:44

How to read such a XML file using CROSS APPLY

<parameter type="LIST">
  <option>
    <name>VALUE SELECTED 1</name>
    <value>0</value>
  </option>
  <option>
    <name>VALUE SELECTED 2</name>
    <value>1</value>
  </option>
  <option>
    <name>VALUE SELECTED 3</name>
    <value>2</value>
  </option>
  <option>
    <name>VALUE SELECTED 4</name>
    <value>3</value>
  </option>
</parameter>

The query i use always returns NULL...

Select x.param.value('Label[1]','varchar(200)') as ParamValue
from  (Select [XMLData] as param FROM MYTABLE 
Cross apply param.nodes('parameter/option')x(param)

MYTABLE : has the XMLData column with all the XML Data present inside

Could anyone please help me in forming this query
Regards
Srivatsa

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-11-02 : 05:42:45

quote:


Select x.param.value('Label[1]','varchar(200)') as ParamValue
from  (Select [XMLData] as param FROM MYTABLE 
Cross apply param.nodes('parameter/option')x(param)

There is no element called label in your xml tag..

create Table MYTABLE (XMLData XML)

insert MYTABLE values('<parameter type="LIST">
<option>
<name>VALUE SELECTED 1</name>
<value>0</value>
</option>
<option>
<name>VALUE SELECTED 2</name>
<value>1</value>
</option>
<option>
<name>VALUE SELECTED 3</name>
<value>2</value>
</option>
<option>
<name>VALUE SELECTED 4</name>
<value>3</value>
</option>
</parameter>')

Select
x.param.value('name[1]','varchar(200)') as ParamValue
from
(
Select [XMLData] as param FROM MYTABLE
) a
Cross apply
param.nodes('parameter/option')x(param)

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-11-02 : 06:34:35

So dumb of me

Thanks Sanoj

quote:
Originally posted by sanoj_av

quote:

Select x.param.value('Label[1]','varchar(200)') as ParamValue
from (Select [XMLData] as param FROM MYTABLE
Cross apply param.nodes('parameter/option')x(param)

There is no element called label in your xml tag..

create Table MYTABLE (XMLData XML)

insert MYTABLE values('<parameter type="LIST">
<option>
<name>VALUE SELECTED 1</name>
<value>0</value>
</option>
<option>
<name>VALUE SELECTED 2</name>
<value>1</value>
</option>
<option>
<name>VALUE SELECTED 3</name>
<value>2</value>
</option>
<option>
<name>VALUE SELECTED 4</name>
<value>3</value>
</option>
</parameter>')

Select
x.param.value('name[1]','varchar(200)') as ParamValue
from
(
Select [XMLData] as param FROM MYTABLE
) a
Cross apply
param.nodes('parameter/option')x(param)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources