Solved

SQL Parse XML - Getting multiple records

Posted on 2011-03-22
4
303 Views
Last Modified: 2012-05-11
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
Comment
Question by:lrbrister
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
use OUTER APPLY instead of CROSS APPLY
0
 
LVL 6

Accepted Solution

by:
openshac earned 500 total points
Comment Utility
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
 
LVL 6

Expert Comment

by:openshac
Comment Utility
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
 

Author Closing Comment

by:lrbrister
Comment Utility
That did it...thanks
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now