Larry Brister
asked on
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...
This is the output
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
This is the output
<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>
use OUTER APPLY instead of CROSS APPLY
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
)
ASKER
That did it...thanks