asked on

Reading XML into MS SQL

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

Anthony Perkins

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.

emailamos

ASKER

Will be using SQL 2005 to retrieve the data.

Anthony Perkins

Ok. Can you now post what you intend to do with the data? Structure of the tables, etc.

emailamos

ASKER

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

Anthony Perkins

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.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

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

Start Free Trial

Anthony Perkins

I had to remove the rest of the code as EE was rejecting it. But you should get the idea.

emailamos

ASKER

Thanks