CODEWITHSUNDEEP
sqlxmlparsing
Joe B
Joe B

Reputation: 1

Parse XML field into table

I am trying to parse an XML field using SQL into a table and I need a little help starting. An example of the XML field for one row is as follows:

<MarketValueTransactionVo
    objectId="104" statusCode="0" acctNum="60835733" recType="6"
    errorFlag="N" sourceCode="0" userId="DATAEXCHANGE" taxItem="0"
    amount="4496.79" accountEntityType="0" transactionAmount="4496.79"
    importFormatType="5" dateEntered="01252015" clearingFirmBrokerCode="OPSX"
    formattedAmount="$4,496.79" totalShares="0" controlNumberSequence="0"
    applicableYear="2014" brokerCode="OPSX" ssn="632248334"
    entityId="OPSX" entityTypeCode="4" activityApplicationCode="3001"
    activityTypeCode="801" entityPresentationNumber="0" checkStatusCode="0"
    correctionCode="0" correctionTypeCode="0" entityLOBCode="0"
    requestPresentationNumber="0" requestStatusCode="0" reverseReasonCode="0"
    loanPresentationNumber="0">
</MarketValueTransactionVo>

Upvotes: 0

Views: 177

Answers (1)

Orest Hera
Orest Hera

Reputation: 6776

You want to address XML tag attributes. Tag attributes can be addressed using at-sign symbol @, see for examples Import XML with Attribute to SQL Server Table and Convert Xml to Table SQL Server (the second format in the answer):

SELECT
   Tbl.Col.value('@objectId', 'int'),  
   Tbl.Col.value('@statusCode', 'tinyint'),  
   Tbl.Col.value('@acctNum', ...proper type int? varchar(xx)? ),
   ...

FROM   @xml.nodes('//MarketValueTransactionVo') Tbl(Col)

Upvotes: 1

Related Questions