Using FLOWR to parse xml file

Question

Using FLOWR to parse xml file

sharon cohen

SSC Rookie

Points: 38
More actions
September 7, 2006 at 3:29 am

#94539

i have sql-server 2005
the xml structure saved in table with XML type(XmlOrderFile xml)
/*
create table TempOrder (seq int identity primary key, XmlOrderFile xml)
*/
the xml structure is :
<ROOT>
<Order>
      <OrderID>123</OrderID>
      <orderStatus>OK</orderStatus>
      <commments>
        <ordcomment>
            <Comment>Hi   123</Comment>
             <UserName>sharon </UserName>
        </ordcomment>
        <ordcomment>
            <Comment>Bye 123</Comment>
            <UserName>Sharon </UserName>
        </ordcomment>
    </commments>
</Order>
<Order>
      <OrderID>124</OrderID>
      <orderStatus>Not OK</orderStatus>
    <commments>
        <ordcomment>
            <Comment>Hi 124</Comment>
             <UserName>BOSS</UserName>
        </ordcomment>
    </commments>
</Order>
</ROOT>
i need to parse the XML file into the follow table structure
create table #order (
                               IPK int identity primary key,
                               ID integer,
                               OrderStatus varchar(10),
                               Comment varchar(50) ,
                              UserName varchar(50)
                              )

table structure
IPK ID OrderStatus COMMENT UserName
1 123 OK  Hi    123  sharon
2 123 OK  Bye 123  sharon
3 124 NOT OK  Hi    124  BOSS
How can i parse the file ?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

DanKennedy Hall of Fame Points: 3736 More actions · Answer 1

I wouldn't use FLWOR to generate the tabular view as you don't need the iteration capability. You'd be better off using OPENXML similar to the following:

DECLARE @XmlDocument xml,
@DocHandle int,
@ErrorCode int

SELECT@XmlDocument = XMLOrderFile FROM TestData WHERE RowID = 1
SELECT  @DocHandle = 0,
@ErrorCode = 0

EXEC @ErrorCode = sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument

IF @ErrorCode = 0
SELECTOrderID,
OrderStatus,
Comment,
UserName
FROMOPENXML (@DocHandle, 'ROOT/Order/commments/ordcomment', 2)
WITH
(
OrderIDint     '../../OrderID',
OrderStatusvarchar(10)  '../../orderStatus',
Commentvarchar(50)  'Comment',
UserNamevarchar(50)  'UserName'
)
ELSE
SELECT @ErrorCode

IF @DocHandle IS NOT NULL AND @DocHandle <> 0
EXEC sp_xml_removedocument @DocHandle

Dan
www.firstcs.co.uk

sharon cohen SSC Rookie Points: 38 More actions · Answer 2

sharon cohen

SSC Rookie

Points: 38

September 10, 2006 at 1:37 am

#659517

OK it's working

thanks

sharon