Solved

Parsing XML into SQL

Posted on 2014-07-23
2
376 Views
Last Modified: 2014-07-23
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>
0
Comment
Question by:Jordan_WM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 34

Accepted Solution

by:
ste5an earned 435 total points
ID: 40215055
Well, it would have been nice, if you would include your solutions so far.. E.g.
DECLARE @Xml XML; 
SET @Xml = N'
<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>
';

SELECT	InvestmentVehicle.value('@_Id', 'NVARCHAR(255)') AS ID ,
		Name.value('@LanguageId', 'NVARCHAR(255)') AS NameLanguageID ,
		Name.value('.', 'NVARCHAR(255)') AS NameText ,
		LegalName.value('@LanguageId', 'NVARCHAR(255)') AS LegalNameLanguageID ,
		LegalName.value('.', 'NVARCHAR(255)') AS LegalNameText ,
		FundServId.value('@CurrencyId', 'NVARCHAR(255)') AS FundServIdCurrencyId ,
		FundServId.value('.', 'NVARCHAR(255)') AS FundServIdText
FROM	@Xml.nodes('/Package/InvestmentVehicle') A ( InvestmentVehicle )
	CROSS APPLY InvestmentVehicle.nodes('Name') B ( Name )
	CROSS APPLY InvestmentVehicle.nodes('LegalName') C ( LegalName ) 
	CROSS APPLY InvestmentVehicle.nodes('FundServList/FundServId') D ( FundServId ) ;

Open in new window

0
 

Author Closing Comment

by:Jordan_WM
ID: 40215225
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
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 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