Solved

Parsing XML into SQL

Posted on 2014-07-23
2
366 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
2 Comments
 
LVL 32

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now