get the value from XML in a sql table

5 pts.
Tags:
SQL Developer
TOAD
i created a table which contains many columns. out of that one column is having xml codings which are saved as xml info. 
We are having number of parameters in one row data of that xml info. Like this we are having multiple rows. I want to select the value from the xml column of that table using sql select statement.
The number of parameters in that xml info is getting updated frequently. I want the count of the params in that xml info after updation happend.
I guess, My question was clear.
Please help. and Thanks in Advance


Software/Hardware used:
SQL DEVELOPER

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Walrum
    I’m not sure that I properly understand your question however below is the sample that perhaps can help you to understand the process of retrieving data from XML using SQL Statement. This sample is actually from Books Online and I believe that you should dig deeper in BO in order to use that and other statements. Here you are: -- Create tables for later population using OPENXML. CREATE TABLE Customers (CustomerID varchar(20) primary key, ContactName varchar(20), CompanyName varchar(20)) GO CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime) GO DECLARE @docHandle int DECLARE @xmlDocument nvarchar(max) -- or xml type SET @xmlDocument = N'<ROOT> <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1"> <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/> <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/> </Customers> <Customers CustomerID="XYZBB" ContactName="Steve" CompanyName="Company2">No Orders yet! </Customers> </ROOT>' EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument -- Use OPENXML to provide rowset consisting of customer data. INSERT Customers SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers') WITH Customers -- Use OPENXML to provide rowset consisting of order data. INSERT Orders SELECT * FROM OPENXML(@docHandle, N'//Orders') WITH Orders -- Using OPENXML in a SELECT statement. SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime) -- Remove the internal representation of the XML document. EXEC sp_xml_removedocument @docHandle If you will understand that sample I believe that getting more option should not create any problem
    270 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following