To retrieve column values from an xmlstring using for xml clause

Question

0.00/5 (No votes)

See more:

Hi..

In our database table one column is stored as an xml string.which is shown below.

XML

<DocumentElement>
  <PartInfo>
    <ID>0</ID>
    <Name />
    <PartNo>0</PartNo>
    <SerialNo>1</SerialNo>
    <Parameter>0</Parameter>
    <InstalledDate>2013-01-15T00:00:00+05:30</InstalledDate>
    <InstalledTill>2013-01-25T00:00:00+05:30</InstalledTill>
  </PartInfo>
  </DocumentElement>

i want to get column values of this string.For example i have to get the value 2013-01-15T00:00:00+05 of installed Date column.Pls tell me how can i obtain this using forxml clause..Help me please i want it urgently..Thats why..Thanks in advance..

swathi.

Posted 5-Feb-13 19:55pm

A C swathi

Updated 5-Feb-13 22:34pm

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)

Tharaka MTR · Answer 1 · 2013-02-05T23:03:00

Solution 1

Suppose you table is MyXMLData and the column name is XmlDataColumn

SQL

SELECT [XmlDataColumn].query('.//InstalledDate').value('.','VARCHAR(MAX)')
FROM [MyXMLData]

With regarding your comments, please check following query

SQL

SELECT [XmlDataColumn].value('(//ID)[1]', 'VARCHAR(MAX)') AS ID, [XmlDataColumn].value('(//PartNo)[1]', 'VARCHAR(MAX)') AS PartNo
FROM [MyXMLData]
WHERE [XmlDataColumn].value('(//parent::node()/InstalledDate)[1]', 'VARCHAR(MAX)') ='2013-01-15T00:00:00+05:30'

Posted 5-Feb-13 23:03pm

Tharaka MTR

Updated 6-Feb-13 18:59pm

v2

Comments

A C swathi 6-Feb-13 6:05am

Thank u..I tried this.But I want to search whole rows based on the value in column and have to bind the rows satisfying this conditon(whose installed date is same as what we type for search in UI).But i cant do it with this code.Because i cant select directly the column because which is now present as an xml string..plz tell me a solution for that..

Tharaka MTR 7-Feb-13 0:59am

I have update my answer, please check whether it is OK.

A C swathi 7-Feb-13 1:36am

Thank u..bt the pblm is still existing.i am getting my table as xmlstring in pgm so cant specify the columns inside that in a select query..i just want the values inside the columns ,i cant specify the value directly in query.Because the value will be changed in xml string depending on the user.i have to fetch the datas based on what he is passing..There may be thousands of user so how can i do this..Is there any solution for that..Thanks in advance..

Tharaka MTR 7-Feb-13 4:06am

What do you mean can't specify the columns inside that?
you said you got the following XML

<documentelement>
<PartInfo>
<id>0
<name>
<PartNo>0</PartNo>
<serialno>1
<Parameter>0</Parameter>
<installeddate>2013-01-15T00:00:00+05:30
<installedtill>2013-01-25T00:00:00+05:30
</PartInfo>

what do you mean I can't specify the value directly in query?
is that you mean "WHERE [XmlDataColumn].value('(//parent::node()/InstalledDate)[1]', 'VARCHAR(MAX)') ='2013-01-15T00:00:00+05:30'" section?

A C swathi 7-Feb-13 4:27am

yes.i meant that section.i have to use this query in a pgm.in that cant write the column xmldatacolumn directly.coz which is now present in xmlstring..at that time what i can do..Plz help me.

Tharaka MTR 7-Feb-13 5:04am

Sorry, I can't understand why is that as you said "In our database table one column is stored as an xml string".

Could you please let me know your table column details?

Tharaka MTR 7-Feb-13 6:32am

Ok. plz let us know how do you resolve this

A C swathi 7-Feb-13 7:04am

sorry..i jst tried whether the value is passing by putting breakpoint.Then its k.bt when running its showing an exception .which is "Cannot find either column "PartInfo" or the user-defined function or aggregate "PartInfo.query", or the name is ambiguous".where partinfo is the column name,that column content is in the form of xmlstring.and i have to get the value of installed date from this xml string and the partinfo is one of the column present in a table called test.how can i solve the exception..i used the first query that u have given
.

Tharaka MTR 7-Feb-13 10:33am

please send me your code sample

A C swathi 7-Feb-13 22:29pm

i have just tried this in a local application.This pgm is to bind the value of installed date to textbox.Below given the code sample.

using System;
using System.Collections.Generic;
using System.Linq;

using System.Text;
using System.Windows;
using System.Windows.Controls;

using System.Windows.Data;
using System.Windows.Documents;

using System.Windows.Input;
using System.Windows.Media;

using System.Windows.Media.Imaging;
using System.Windows.Navigation;

using System.Windows.Shapes;
using System.Data;
using System.Data.SqlClient;

using System.Xml;
using System.IO;
namespace WpfApplication1{ ///

/// Interaction logic for MainWindow.xaml ///

public partial class MainWindow : Window
{
public MainWindow()

{
InitializeComponent();
try
{

SqlConnection con = new SqlConnection("Data Source=NIP007\\SQLEXPRESS;Initial Catalog=test;User ID=sa;Password=nest123@!");

con.Open();

string query = "SELECT [partinfo].query('.//InstalledDate').value('.','VARCHAR(MAX)')FROM [test]";

SqlCommand cmd = new SqlCommand(query, con);

// StringBuilder builder=new StringBuilder();

// string PartInfo=string.Empty;

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

string str5 =dr.ToString();

if (!string.IsNullOrEmpty(str5))

{
textBox1.Text=str5;

}
}

}
catch(Exception ex)
{

}
}
}}

but its showing exception as i said earlier..