Solved

Reading XML into MS SQL

Posted on 2009-04-03
8
1,635 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
  • 5
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
Will be using SQL 2005 to retrieve the data.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Ok.  Can you now post what you intend to do with the data?  Structure of the tables, etc.
0
 

Author Comment

by:emailamos
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 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

7 Experts available now in Live!

Get 1:1 Help Now