Parse XML field into table

Question

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>

Where is example? Which database? – Tatiana Oct 12 '15 at 13:16 — Tatiana, Oct 12 '15 at 13:16

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

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)

edited May 23 '17 at 12:14

Community

1
1

answered Oct 12 '15 at 21:51

Orest Hera

6,706
2
21
35

1

one upvote from my side and a tiny hint: You should not use the doubled slash (`nodes('//...`) if you don't need it. Its always better to address elements with an exact path. In a more complex scenario you never know, if an element's name is not repeated on a deeper level... – Shnugo Oct 13 '15 at 13:39

Parse XML field into table

1 Answers1