get the value from XML in a sql table
i created a table which contains many columns. out of that one column is having xml codings which are saved as xml info.
Software/Hardware used:
SQL DEVELOPER
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
ASKED:
December 19, 2011 10:17 AM
UPDATED:
February 28, 2012 2:48 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
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