XML query fetch attributes as records/rows

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)

XML query fetch attributes as records/rows

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-07 : 13:56:08

I have below xml and want to fetch all records as rows. My xml is like below..



<category ccode="ct8">
  <columns>
    <col colcode="cl_prodn" displaytext="Prodname" responsetype="textbox" tooltip="testts" isrequired="" displayorder="1" />
    <col colcode="cl_descs" displaytext="Descs" responsetype="textarea" tooltip="atser" isrequired="on" displayorder="2" />
  </columns>
</category>

I want two rows for category ccode = ct8. Those two rows will show all attributes. I am trying with below query but it returns only one and first.

select CatConfig.value('(category/columns/col/@colcode)[1]', 'varchar(50)') from categories where CategoryId = 8

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-07 : 14:39:39

Change to this:

select c2.value('@colcode', 'varchar(50)') 
from categories c
CROSS APPLY c.CatConfig.nodes('/category/columns/col') T(c2)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 00:41:43

quote:

Originally posted by James K

Change to this:

select c2.value('@colcode', 'varchar(50)') 
from categories c
CROSS APPLY c.CatConfig.nodes('/category/columns/col') T(c2)

I think it still needs a condition check inside category node to make sure only the ones with @ccode = ct8 is returned

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-08 : 08:56:51

Yes, nice.
Great thanks to visakh and james

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 12:51:41

[code]
declare @x xml='<categories>
<category ccode="ct8">
<columns>
<col colcode="cl_prodn1" displaytext="Prodname1" responsetype="textbox1" tooltip="testts1" isrequired="" displayorder="1" />
<col colcode="cl_descs1" displaytext="Descs1" responsetype="textarea1" tooltip="atser1" isrequired="on1" displayorder="2" />
</columns>
</category>
<category ccode="ct10">
<columns>
<col colcode="cl_prodn2" displaytext="Prodname2" responsetype="textbox2" tooltip="testts2" isrequired="" displayorder="1" />
<col colcode="cl_descs2" displaytext="Descs2" responsetype="textarea2" tooltip="atser2" isrequired="on23" displayorder="2" />
</columns>
</category>
<category ccode="ct9">
<columns>
<col colcode="cl_prodn3" displaytext="Prodname3" responsetype="textbox3" tooltip="testts3" isrequired="" displayorder="1" />
<col colcode="cl_descs3" displaytext="Descs" responsetype="textarea3" tooltip="atser3" isrequired="on3" displayorder="2" />
</columns>
</category>
</categories>'

SELECT t.u.value('@colcode[1]','varchar(100)') AS colcode,
t.u.value('@displaytext[1]','varchar(100)') AS displaytext,
t.u.value('@responsetype[1]','varchar(100)') AS responsetype,
t.u.value('@tooltip[1]','varchar(100)') AS tooltip,
t.u.value('@isrequired[1]','varchar(100)') AS isrequired,
t.u.value('@displayorder[1]','varchar(100)') AS displayorder
FROM @x.nodes('/categories/category[./@ccode="ct8"]/columns/col') t(u)

output
-------------------------------------------------------------------------------------------------------
colcode displaytext responsetype tooltip isrequired displayorder
-------------------------------------------------------------------------------------------------------
cl_prodn1 Prodname1 textbox1 testts1 1
cl_descs1 Descs1 textarea1 atser1 on1 2

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-09 : 01:52:40

Yes, good one visakh. you are great.

That is what I was looking for. thx.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-09 : 02:05:56

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources