Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Reading XML into MS SQL

Posted on 2009-04-03
8
Medium Priority
?
1,653 Views
Last Modified: 2013-11-18
I'm trying to read attached xml into SQL, how to I do it.
<report_output>
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
     xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
     xmlns:rs='urn:schemas-microsoft-com:rowset'
     xmlns:z='#RowsetSchema'
     content="Report"
     title="Mod"
     resultHeading="Mod"
     resultName="Data"
     runat="01/04/09"
     user="a"
     database="ACC"
     version="5.3"
     applicationdate="02/04/09"
     >
  <s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly'>
<s:attribute type='GeneratedPK' />      <s:attribute type='RiskName'/>
      <s:attribute type='TradeType'/>
      <s:attribute type='Value'/>
      <s:extends type='rs:rowbase'/>
    </s:ElementType>
<s:AttributeType name='GeneratedPK' rs:number='1' rs:maybenull='false' 
rs:keycolumn='true' rs:autoincrement='true' rs:writeunknown='true'>
<s:datatype dt:type='int' />
</s:AttributeType>
    <s:AttributeType name='RiskName' rs:number='2' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='string' dt:maxLength='200' origDataType='STRING'/>
    </s:AttributeType>
    <s:AttributeType name='TradeType' rs:number='3' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='string' dt:maxLength='200' origDataType='STRING'/>
    </s:AttributeType>
    <s:AttributeType name='Value' rs:number='4' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='number' dt:maxLength='50' origDataType='AMT   '/>
    </s:AttributeType>
  </s:Schema>
  <rs:data>
  <z:row
 GeneratedPK='1'
    RiskName='Interest Rate : AUD/XXX'
    TradeType='BPS/BOND'
    Value='17.605'
  />
  <z:row
 GeneratedPK='2'
    RiskName='Interest Rate : AUD/XXXX'
    TradeType='BPS/MM'
    Value='-22.0675'
  />
  <z:row
 GeneratedPK='3'
    RiskName='Interest Rate : AUD/XXXX'
    TradeType='BPS/FXSWAP'
    Value='122.483'
  />
  <z:row
 GeneratedPK='4'
    RiskName='Interest Rate : AUD/XXXX'
    TradeType='TOTAL BPS'
    Value='18.020925'
  />
  <z:row
 GeneratedPK='5'
    RiskName='Interest Rate : AUD/FRNAA'
    TradeType='TOTAL BPS'
    Value='-1.0000001'
  />
  <z:row
 GeneratedPK='6'
    RiskName='Interest Rate : AUD/C'
    TradeType='TOTAL BPS'
    Value='-199.5032'
  />
  <z:row
 GeneratedPK='7'
    RiskName='Interest Rate : USD/LIID'
    TradeType='TOTAL BPS'
    Value='-76.78061'
  />
  <z:row
 GeneratedPK='8'
    RiskName='Interest Rate : USD/C'
    TradeType='TOTAL BPS'
    Value='83.128'
  />
  </rs:data>
</xml>
 
 
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
     xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
     xmlns:rs='urn:schemas-microsoft-com:rowset'
     xmlns:z='#RowsetSchema'
     content="Report"
     title="Mod"
     resultHeading="Mod"
     resultName="Totals"
     runat="01/04/09"
     user="a"
     database="ACC"
     version="5.3"
     applicationdate="02/04/09"
     >
  <s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly'>
<s:attribute type='GeneratedPK' />      <s:attribute type='PortfolioBPS'/>
      <s:attribute type='PortfolioMTM'/>
      <s:attribute type='ModifiedDuration'/>
      <s:extends type='rs:rowbase'/>
    </s:ElementType>
<s:AttributeType name='GeneratedPK' rs:number='1' rs:maybenull='false' 
rs:keycolumn='true' rs:autoincrement='true' rs:writeunknown='true'>
<s:datatype dt:type='int' />
</s:AttributeType>
    <s:AttributeType name='PortfolioBPS' rs:number='2' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='number' dt:maxLength='16' origDataType='AMT   '/>
    </s:AttributeType>
    <s:AttributeType name='PortfolioMTM' rs:number='3' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='number' dt:maxLength='16' origDataType='AMT   '/>
    </s:AttributeType>
    <s:AttributeType name='ModifiedDuration' rs:number='4' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='number' dt:maxLength='16' origDataType='AMT   '/>
    </s:AttributeType>
  </s:Schema>
  <rs:data>
  <z:row
 GeneratedPK='1'
    PortfolioBPS='-59.61848'
    PortfolioMTM='428917.924968'
    ModifiedDuration='0.18443'
  />
  </rs:data>
</xml>
</report_output>

Open in new window

0
Comment
Question by:emailamos
[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
  • 5
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24065463
It really depends which version.  With SQL Server 2000 all you have is OPENXML().  With SQL Server 2005 there are Xml methods you can use to retrieve the data.

Unfortunately without knowing what verions you are using and what you intend to do exactly with the Xml it is difficult to help any further.
0
 

Author Comment

by:emailamos
ID: 24071347
Will be using SQL 2005 to retrieve the data.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24071976
Ok.  Can you now post what you intend to do with the data?  Structure of the tables, etc.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:emailamos
ID: 24074770
What I want to grab the values of these columns (RiskName ,TradeType , Value , PortfolioBPS,PortfolioMTM ,ModifiedDuration )
Tried below, but I don't know how to transverse the XML tree

DROP TABLE #WorkingTable
CREATE TABLE #WorkingTable
(Data XML)


INSERT INTO #WorkingTable
SELECT * FROM OPENROWSET (BULK 'C:\Temp\Output_Files\report_output.xml', SINGLE_BLOB) AS data

select * from #WorkingTable
-- to read the XML from the working table:
DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = Data FROM #WorkingTable

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc, '/report_output/xml/data/row', 2)
WITH (
RiskName varchar(200) '../@RiskName',
TradeType varchar(200) '../@TradeType',
Value decimal(38,4) '../@Value',
PortfolioBPS decimal(16,8) '../@PortfolioBPS',
PortfolioMTM decimal(16,8) '../@PortfolioMTM',
ModifiedDuration decimal(16,8) '../@ModifiedDuration')

EXEC sp_xml_removedocument @hDoc
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24080262
First of all Xml documents do not typically use apostrophes (single quotes) for the values, but rather quotation marks (double quotes).

But to answer your question you failed to include the Xml namespace and the XPaths are incorrect.

I am trying to post the solution, but it is getting rejected.  May have to abreviate it.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 24080270
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, '<Root xmlns:rs=''urn:schemas-microsoft-com:rowset'' xmlns:z=''#RowsetSchema''/>'

SELECT      RiskName,
      TradeType,
      [Value],
      PortfolioBPS,
      PortfolioMTM,
      ModifiedDuration
FROM OPENXML(@hDoc, '/report_output/xml/rs:data/z:row', 2) WITH (
      RiskName varchar(200) '@RiskName',
      TradeType varchar(200) '@TradeType',
      [Value] decimal(38,4) '@Value',
      PortfolioBPS decimal(16,8) '@PortfolioBPS',
      PortfolioMTM decimal(16,8) '@PortfolioMTM',
      ModifiedDuration decimal(16,8) '@ModifiedDuration'
      )
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24080277
I had to remove the rest of the code as EE was rejecting it.  But you should get the idea.
0
 

Author Closing Comment

by:emailamos
ID: 31566147
Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

610 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