I'm trying to flatten my XML file however there are complications when there are duplicate tags within a tag. I'm not sure how to move back up the tag chain. Examples below.
DECLARE @xml AS XMl, @hDoc AS INT
SET @xml = '<Message>
<Body>
<SfkpgAcctAndHldgs>
<SfkpgAcct>UHS5465</SfkpgAcct>
<AcctSubLvl>
<Dsclsr>
<SfkpgAcct>450812361</SfkpgAcct>
<AcctHldr>
<LglPrsn>
<NmAndAdr>
<Nm>Foo</Nm>
<Adr>
<AdrLine>1 CORPORATE WAY, New York 15951</AdrLine>
<Ctry>US</Ctry>
</Adr>
</NmAndAdr>
<LEI>5493231HPIWHJS5QL7N39</LEI>
<Ownrsh>OWNR</Ownrsh>
</LglPrsn>
</AcctHldr>
<ShrhldgBal>
<ShrhldgTp>BENE</ShrhldgTp>
<Unit>1000</Unit>
</ShrhldgBal>
</Dsclsr>
<Dsclsr>
<SfkpgAcct>450812362</SfkpgAcct>
<AcctHldr>
<LglPrsn>
<NmAndAdr>
<Nm>Bar</Nm>
<Adr>
<AdrLine>2 CORPORATE WAY</AdrLine>
<AdrLine>New York</AdrLine>
<AdrLine>15951</AdrLine>
<Ctry>US</Ctry>
</Adr>
</NmAndAdr>
<LEI>5493231HP2342345QL7N39</LEI>
<Ownrsh>OWNR</Ownrsh>
</LglPrsn>
</AcctHldr>
<ShrhldgBal>
<ShrhldgTp>BENE</ShrhldgTp>
<Unit>300</Unit>
</ShrhldgBal>
</Dsclsr>
</AcctSubLvl>
</SfkpgAcctAndHldgs>
</Body>
</message>'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
This query takes each SfkpgAcctAndHldgs and brings in the first Dsclsr, there are two but only the first it returned.
SELECT *
FROM OPENXML(@hDoc,'message/Body/SfkpgAcctAndHldgs') WITH (
SfkpgAcct NVARCHAR(MAX) 'SfkpgAcct'
, Dsclsr_SfkpgAcct NVARCHAR(MAX) 'AcctSubLvl/Dsclsr/SfkpgAcct'
, Dsclsr_AcctHldr_LglPrsn_NmAndAdr_Nm NVARCHAR(MAX) 'AcctSubLvl/Dsclsr/AcctHldr/LglPrsn/NmAndAdr/Nm'
, Dsclsr_AcctHldr_LglPrsn_NmAndAdr_Adr_AdrLine NVARCHAR(MAX) 'AcctSubLvl/Dsclsr/AcctHldr/LglPrsn/NmAndAdr/Adr/AdrLine'
, Dsclsr_AcctHldr_LglPrsn_NmAndAdr_Adr_Ctry NVARCHAR(MAX) 'AcctSubLvl/Dsclsr/AcctHldr/LglPrsn/NmAndAdr/Adr/Ctry'
, Dsclsr_AcctHldr_LglPrsn_LEI NVARCHAR(MAX) 'AcctSubLvl/Dsclsr/AcctHldr/LglPrsn/LEI'
, Dsclsr_AcctHldr_LglPrsn_Ownrsh NVARCHAR(MAX) 'AcctSubLvl/Dsclsr/AcctHldr/LglPrsn/Ownrsh'
, Dsclsr_ShrhldgBal_ShrhldgTp NVARCHAR(MAX) 'AcctSubLvl/Dsclsr/ShrhldgBal/ShrhldgTp'
, Dsclsr_ShrhldgBal_Unit NVARCHAR(MAX) 'AcctSubLvl/Dsclsr/ShrhldgBal/Unit'
)
--SfkpgAcct AcctSubLvl_Dsclsr_SfkpgAcct Dsclsr_AcctHldr_LglPrsn_NmAndAdr_Nm Dsclsr_AcctHldr_LglPrsn_NmAndAdr_Adr_AdrLine Dsclsr_AcctHldr_LglPrsn_NmAndAdr_Adr_Ctry Dsclsr_AcctHldr_LglPrsn_LEI Dsclsr_AcctHldr_LglPrsn_Ownrsh Dsclsr_AcctHldr_ShrhldgBal_ShrhldgTp Dsclsr_AcctHldr_ShrhldgBal_Unit
--UHS5465 450812361 Foo 1 CORPORATE WAY, New York 15951 US 5493231 HPIWHJS5QL7N39 OWNR BENE 1000
This query looks at the Dsclsr level and so brings back both lines. However I can't go back up the Chain to get the first SfkpgAcct value, which is a unique value I can join the tables on.
SELECT *
FROM OPENXML(@hDoc,'message/Body/SfkpgAcctAndHldgs/AcctSubLvl/Dsclsr') WITH (
SfkpgAcct NVARCHAR(MAX) 'SfkpgAcct'
, AcctHldr_LglPrsn_NmAndAdr_Nm NVARCHAR(MAX) 'AcctHldr/LglPrsn/NmAndAdr/Nm'
, AcctHldr_LglPrsn_NmAndAdr_Adr_AdrLine NVARCHAR(MAX) 'AcctHldr/LglPrsn/NmAndAdr/Adr/AdrLine'
, AcctHldr_LglPrsn_NmAndAdr_Adr_Ctry NVARCHAR(MAX) 'AcctHldr/LglPrsn/NmAndAdr/Adr/Ctry'
, AcctHldr_LglPrsn_LEI NVARCHAR(MAX) 'AcctHldr/LglPrsn/LEI'
, AcctHldr_LglPrsn_Ownrsh NVARCHAR(MAX) 'AcctHldr/LglPrsn/Ownrsh'
, ShrhldgBal_ShrhldgTp NVARCHAR(MAX) 'ShrhldgBal/ShrhldgTp'
, ShrhldgBal_Unit NVARCHAR(MAX) 'ShrhldgBal/Unit'
)
--SfkpgAcct AcctHldr_LglPrsn_NmAndAdr_Nm AcctHldr_LglPrsn_NmAndAdr_Adr_AdrLine AcctHldr_LglPrsn_NmAndAdr_Adr_Ctry AcctHldr_LglPrsn_LEI AcctHldr_LglPrsn_Ownrsh ShrhldgBal_ShrhldgTp ShrhldgBal_Unit
--450812361 Foo 1 CORPORATE WAY, New York 15951 US 5493231HPIWHJS5QL7N39 OWNR BENE 1000
--450812362 Bar 2 CORPORATE WAY US 5493231HP2342345QL7N39 OWNR BENE 300
Also there are on occasions multiple AdrLine fields however in the same example as above I do not know who to go back up the chain to reference any previous values so I can then join on.
SELECT *
FROM OPENXML(@hDoc,'message/Body/SfkpgAcctAndHldgs/AcctSubLvl/Dsclsr/AcctHldr/LglPrsn/NmAndAdr/Adr/AdrLine') WITH (
AcctHldr_LglPrsn_NmAndAdr_Adr_AdrLine NVARCHAR(MAX) '.'
)
--AcctHldr_LglPrsn_NmAndAdr_Adr_AdrLine
--1 CORPORATE WAY, New York 15951
--2 CORPORATE WAY
--New York
--15951
EXEC sp_xml_removedocument @hDoc
Ideally, I want all values in a flat file, I can use a view to join the individual tables together providing I can attribute an existing ID or generate a new one.
--SfkpgAcct DsclsrSfkpgAcct Nm AdrLine1 AdrLine2 AdrLine3 Ctry LEI Ownrsh ShrhldgTp Unit
--UHS5465 450812361 Foo 1 CORPORATE WAY, New York 15951 US 5493231HPIWHJS5QL7N39 OWNR BENE 1000
--UHS5465 450812362 Bar 2 CORPORATE WAY New York 15951 US 5493231HP2342345QL7N39 OWNR BENE 300
