CODEWITHSUNDEEP
sql-serverxmlxml-parsingssis
Jay C
Jay C

Reputation: 872

Is it possible to Parse this XML file using SQL Server or SSIS?

I have an XML file I receive from a financial data provider called MDM. It shows which dividends I have downloaded. It includes information like Dividend rate, date and the security requested.

I have tried to parse this with SSIS using the XML source and Merge Join. I also tried SQL Server 2012 using Open XML and couldn't do it. I gathered these techniques from Youtube, google and searching this board. My goal is for this to be in an easy to read table format so I can see which securities received dividend information.

When I tried to Parse it with SSIS the following output names were given

FIELD
SECURITY
APP_PARAM
MDM_MESSAGE

The XML file is too big to post entirely here but I have included a Dropbox link that hopefully works. Hopefully someone can help. I am not sure if this file, though XML can be parsed or not.

https://dl.dropboxusercontent.com/u/29851290/parse_file.xml

Ideally the output will be something like this.

SYM_TYPE_ID   SEC_SYMBOL  SEC_TYPE_ID FOR DATE    EX_DT    PAY_DT     WASH_AMOUNT      RATE TICKER           
aapl          csus        (Not sure)  5/15/2013   6/1/2013 (Not Sure)    0.25

Upvotes: 3

Views: 1639

Answers (1)

Devart
Devart

Reputation: 121902

Try this one -

DECLARE @XML XML
SELECT @XML = CONVERT (XML, [BulkColumn])
FROM OPENROWSET (BULK N'C:\parse_file.xml', SINGLE_BLOB) [XmlData]

SELECT PROV_ID = t.c.value('../@PROV_ID', 'VARCHAR(25)') 
     , SYM_TYPE_ID = t.c.value('../@SYM_TYPE_ID', 'VARCHAR(25)')
     , SEC_SYMBOL = t.c.value('../@SEC_SYMBOL', 'VARCHAR(25)')
     , SEC_TYPE_ID = t.c.value('../@SEC_TYPE_ID', 'VARCHAR(25)')
     , LOCAL_NAME = t.c.value('@LOCAL_NAME', 'VARCHAR(25)')
     , FOR_DATE = t.c.value('@FOR_DATE', 'DATETIME')
     , FIELD = t.c.value('.', 'VARCHAR(25)')
FROM @XML.nodes('root/MDM_MESSAGE[2]/SECURITY/FIELD') t(c)
WHERE t.c.value('@SEC_SYMBOL', 'VARCHAR(25)') = '57636Q104'

UNION ALL

SELECT PROV_ID = t.c.value('@PROV_ID', 'VARCHAR(25)') 
     , SYM_TYPE_ID = t.c.value('@SYM_TYPE_ID', 'VARCHAR(25)')
     , SEC_SYMBOL = t.c.value('@SEC_SYMBOL', 'VARCHAR(25)')
     , SEC_TYPE_ID = t.c.value('@SEC_TYPE_ID', 'VARCHAR(25)')
     , LOCAL_NAME = NULL
     , FOR_DATE = NULL
     , FIELD = NULL
FROM @XML.nodes('root/MDM_MESSAGE[2]/SECURITY') t(c)
WHERE t.c.exist('FIELD') = 0
     AND t.c.value('@SEC_SYMBOL', 'VARCHAR(25)') = '57636Q104'

Upvotes: 3

Related Questions