Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    29

    Unanswered: Data from XML format to individual column in SQL

    Hello I have in one column in SQL 2005 but it is in XML format. How I can conver that data into individual column in select statement.

    for example the column name sourcetext is

    <Order DateFormat="dd-MMM-yyyy" OrderName="aspirin -" SummaryLine="tablet&#xD;&#xA;325 mg oral Every 4 Hours" OrderDate="14-Sep-2011 9:13:00 AM" Status="Active" DiscStopDate="13-Dec-2011 11:59:00 PM" SessionType="Standard" Category="Pharmacy" SessionTypeName="Standard"></Order>

    I need following columns when I retrive the data.

    Order Name, SummaryLine, OrderDate, Status, DiscStopDate, SessionType


    KM

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    SQL Server 2005 Books Online

    XQuery Against the xml Data Type


    Quote Originally Posted by kmistry1 View Post
    Hello I have in one column in SQL 2005 but it is in XML format. How I can conver that data into individual column in select statement.

    for example the column name sourcetext is

    <Order DateFormat="dd-MMM-yyyy" OrderName="aspirin -" SummaryLine="tablet&#xD;&#xA;325 mg oral Every 4 Hours" OrderDate="14-Sep-2011 9:13:00 AM" Status="Active" DiscStopDate="13-Dec-2011 11:59:00 PM" SessionType="Standard" Category="Pharmacy" SessionTypeName="Standard"></Order>

    I need following columns when I retrive the data.

    Order Name, SummaryLine, OrderDate, Status, DiscStopDate, SessionType


    KM

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I had a little spare time and wrote this snippet, but feel free to modify as needed:

    Code:
    CREATE TABLE T (
        OrderID          int primary key, 
        Orders xml)
    Go
    -- insert sample data
    insert into T values(1,'<Order DateFormat="dd-MMM-yyyy" OrderName="aspirin -" SummaryLine="tablet&#xD;&#xA;325 mg oral Every 4 Hours" OrderDate="14-Sep-2011 9:13:00 AM" Status="Active" DiscStopDate="13-Dec-2011 11:59:00 PM" SessionType="Standard" Category="Pharmacy" SessionTypeName="Standard"></Order>')
    go
    -- attribute value from the XML)
      SELECT *,
             Orders.value('(/Order/@OrderName)[1]', 'varchar(40)') as OrderName,
             Orders.value('(/Order/@SummaryLine)[1]', 'varchar(40)') as SummaryLine
      FROM T
    go 
     
    DROP TABLE T

  4. #4
    Join Date
    Feb 2003
    Posts
    29
    Thanks for helping out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •