asked on

Parsing XML into SQL

Hello,

I have the following XML file which has data in the attributes and within the tags I trying to figure out the best approach to get this data into SQL. I am able to pull out the attributes but what happens when I pull out the data with in the tags it is all concatenated together as one value.

What would be the best approach to this multiple passes?

LJ

<Package>
<InvestmentVehicle _Id="F00000Q9RW">
      <Name LanguageId="0L00000122">Need Data From Here</Name>
      <LegalName LanguageId="0L00000122">Need Data From Here</LegalName>
      <FundServList>
       <FundServId CurrencyId="CU$$$$$USD">PMO2406</FundServId>
      </FundServList>
</InvestmentVehicle>
<InvestmentVehicle _Id="F00000LXW6">
      <Name LanguageId="0L00000138">CAN Revenu (M) 100/100</Name>
      <Name LanguageId="0L00000122">CAN Income (M) 100/100</Name>
      <LegalName LanguageId="0L00000138">CAN Revenu (M) 100/100</LegalName>
      <LegalName LanguageId="0L00000122">CAN Income (M) 100/100</LegalName>
      <FundServList>
       <FundServId CurrencyId="CU$$$$$CAD">CAN1073</FundServId>
       <FundServId CurrencyId="CU$$$$$CAD">CAN873</FundServId>
       <FundServId CurrencyId="CU$$$$$CAD">CAN973</FundServId>
      </FundServList>
</InvestmentVehicle>
</Package>

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jordan_WM

ASKER

Thank you wasn't looking for a full solution, I was getting frustrated in trying to go the data out. I ended up getting the right number of records but the columns were not updating with new values

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XmlCol FROM T

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT _Id--, Name--, LegalName, CurrencyId
, FundServId
FROM OPENXML(@hDoc, 'Package/InvestmentVehicle/FundServList/FundServId')
WITH
(
_Id [varchar](50) '../../@_Id',
Name [varchar](100) '../../Name',
LegalName [varchar](100) '../../LegalName',
CurrencyId [varchar](100) '@CurrencyId',
FundServId [varchar](100) '../FundServId'

)

EXEC sp_xml_removedocument @hDoc
GO