Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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
52326 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
52326 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
52326 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  
 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.17 seconds. Powered By: Snitz Forums 2000