• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

SQL Parse XML

My attached code works for all the xml data nodes I'm pulling...EXCEPT where I have

<RecordStatus Status="POSTED"/>

Instead of
<RecordStatusOther>CLEARED</RecordStatusOther>

How do I get that Status="POSTED" part>
-- variable to store the XML content
declare @xml_text varchar(max)

 -- xml document handle             
declare @i int                                         
  
-- get XML content from XML data type field in table into a variable  
select @xml_text = cast(xml_data as varchar(max)) from xmlImportBuffer  
  
-- prepare the XML document  
exec sp_xml_preparedocument @i OUTPUT, @xml_text  
  
-- Get the PacketNum and RunDate at the <Document> node level  
-- and rpeat these fields(CROSS APPLY) for every record at the <Record> node level  
select * from  
        openxml(@i,'/Document',2)  
        with  
        (  
                PacketNum nvarchar(100),  
                RunDate nvarchar(10)  
        )  
CROSS APPLY  
(  
        select * from  
        openxml(@i,'/Document/Record',2)  
        with  
        (  
                SeqNumber int,  
                LienRefNum nvarchar(20),
                DFSNumber nvarchar(20),
                Ref1 nvarchar(1000),  
                Ref2 nvarchar(1000),
                Ref3 nvarchar(1000), 
                Ref4 nvarchar(1000),
                RecordStatus varchar(20),   
                FilingOffice nvarchar(50)  
        )  
) s 


CROSS APPLY  
(  
        select * from  
        openxml(@i,'/Document/Record/FirstDebtorName/IndividualName',2)  
        with  
        (  
                FirstName nvarchar(50),  
                LastName nvarchar(50)  
        )  
) d 
  
-- remove the XML dcoument  
exec sp_xml_removedocument @i

Open in new window

0
lrbrister
Asked:
lrbrister
1 Solution
 
openshacCommented:
Try this:
RecordStatus varchar(20) './RecordStatus/@Status',   

Open in new window

or
RecordStatus varchar(20) 'RecordStatus/@Status',   

Open in new window

           
0
 
lrbristerAuthor Commented:
Excellent!

remind me to buy you a sody-pop sometimes!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now