SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML query fetch attributes as records/rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 05/07/2013 :  13:56:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 05/07/2013 :  14:39:39  Show Profile  Reply with Quote
Change to this:
select c2.value('@colcode', 'varchar(50)') 
from categories c
CROSS APPLY c.CatConfig.nodes('/category/columns/col') T(c2)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/08/2013 :  00:41:43  Show Profile  Reply with Quote
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
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 05/08/2013 :  08:56:51  Show Profile  Reply with Quote
Yes, nice.
Great thanks to visakh and james
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/08/2013 :  12:51:41  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 05/09/2013 :  01:52:40  Show Profile  Reply with Quote
Yes, good one visakh. you are great.

That is what I was looking for. thx.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/09/2013 :  02:05:56  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000