How do dynamic List in Asp.Net and Sql for this?

Question

0.00/5 (No votes)

See more:

I 'm trying to get list of xml values . I have table with xml column like this(count of ID rows in other xml in column is different :

XML

<Category>
  <ID>A50</ID>
  <ID>A51</ID>
  <ID>A52</ID>
  <ID>A53</ID>
</Category>

My sql query is like this :

SQL

DECLARE @id int = 1
 SELECT 
  E.P.value('(ID)[@id]', 'nvarchar(MAX)') 
FROM 
   Table
 CROSS APPLY
     xml.nodes('/Category') AS E(P)

Its not work .But i need to get each node(@id is for this - it will be loop like 1 then 2 etc.) value to asp.net List item/texbox , to change it from webform .
How can i get only values and output it on Asp.net List (i don't know how much will be nodes and it make difficult too) ?

Posted 25-Dec-15 1:27am

Андрей Голубцов

Updated 25-Dec-15 1:29am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Anisuzzaman Sumon · Accepted Answer · 2015-12-25T03:37:00

Solution 1

Here XmlColumn is a comumn of data type xml .Your solution is here.just get is suit for yours and use.If there any problems then please ask.

SQL

  DECLARE @xml_var XML
  select @xml_var=[XmlColumn] from [SubscriberDB].[dbo].[tbl_MyTable]
 
SELECT

  Category.query('./text()')

    AS ID

FROM

  @xml_var.nodes('/Category/ID')

    AS Bike(Category);

OUTPUT:
ID
---
A50
A51
A52
A53

Posted 25-Dec-15 3:37am

Anisuzzaman Sumon

Updated 25-Dec-15 3:42am

v3

Comments

Андрей Голубцов 25-Dec-15 10:06am

I have a question . If i want to send it to asp.net List and make row editable how i can make this ? If i don't know how many rows i will have?