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

SQL Parse XML - Getting multiple records

I have the attached data in my database xml column

When I run this code I get duplicate output...2 of each...it runs once and then again...
-- 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) './RecordStatus/@Status',  
                RecordStatus varchar(20) 'RecordStatus/@Status',    
                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


This is the output
Screenprint
<Document>
  <XMLVersion version="1.04" />
  <PacketNum>366904_21676_20110317</PacketNum>
  <RunDate>20110317</RunDate>
  <Record>
    <SeqNumber>1</SeqNumber>
    <LienRefNum>37331766</LienRefNum>
    <DFSNumber />
    <Ref1>Our UCC Filing</Ref1>
    <Ref2>Portfolio Number</Ref2>
    <Ref3 />
    <Ref4 />
    <RecordStatus Status="InProcess" />
    <FilingType Type="Original" />
    <FileStatus Status="Submitted">03/15/2011 12:01:24 - XML filing submitted to IL</FileStatus>
    <FileNumber />
    <OriginalFileNumber />
    <FileDate />
    <OriginalFileDate />
    <ExpirationDate />
    <FilingState>IL</FilingState>
    <FilingOffice> Secretary of State</FilingOffice>
    <FirstDebtorName>
      <IndividualName>
        <FirstName>JOHN</FirstName>
        <MiddleInit />
        <LastName>BRACEWELL</LastName>
        <Suffix />
      </IndividualName>
    </FirstDebtorName>
    <RejectionReason />
  </Record>
  <Record>
    <SeqNumber>2</SeqNumber>
    <LienRefNum>37331767</LienRefNum>
    <DFSNumber />
    <Ref1>Our UCC Filing 2</Ref1>
    <Ref2>Portfolio Number 2</Ref2>
    <Ref3 />
    <Ref4 />
    <RecordStatus Status="Hold" />
    <FilingType Type="Original" />
    <FileStatus Status="Submitted">03/16/2011 12:01:24 - XML filing submitted to IL</FileStatus>
    <FileNumber />
    <OriginalFileNumber />
    <FileDate />
    <OriginalFileDate />
    <ExpirationDate />
    <FilingState>IL</FilingState>
    <FilingOffice> Secretary of State</FilingOffice>
    <FirstDebtorName>
      <IndividualName>
        <FirstName>MAX</FirstName>
        <MiddleInit />
        <LastName>SMITH</LastName>
        <Suffix />
      </IndividualName>
    </FirstDebtorName>
    <RejectionReason />
  </Record>
</Document>

Open in new window

0
lrbrister
Asked:
lrbrister
  • 2
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
use OUTER APPLY instead of CROSS APPLY
0
 
openshacCommented:
I think the problem lies with you calling the cross apply, you are essentially doing a cross product

The first select returns 1 row
The second select returns 2 rows (CROSS APPLYing returns 1x2)
The third select returns 2 rows (CROSS APPLYing returns 1x2x2 = 4 rows)

Try joining you the results of the select statements as if they were just tables
0
 
openshacCommented:
Or just have this as the whole query:

select * from  
        openxml(@i,'/Document/Record',2)  
        with  
        (  
                PacketNum nvarchar(100) '../PacketNum',  
                RunDate nvarchar(10) '../RunDate',
                SeqNumber int,  
                LienRefNum nvarchar(20),
                DFSNumber nvarchar(20),
                Ref1 nvarchar(1000),  
                Ref2 nvarchar(1000),
                Ref3 nvarchar(1000), 
                Ref4 nvarchar(1000),
                --RecordStatus varchar(20) './RecordStatus/@Status',  
                RecordStatus varchar(20) 'RecordStatus/@Status',    
                FilingOffice nvarchar(50),
                FirstName nvarchar(50) './FirstDebtorName/IndividualName/FirstName',
                LastName nvarchar(50) './FirstDebtorName/IndividualName/LastName' 
        )  

Open in new window

0
 
lrbristerAuthor Commented:
That did it...thanks
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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