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
– Tatiana

2015-10-12 13:16:31 +00:00
Commented Oct 12, 2015 at 13:16 — Tatiana
– Tatiana, Commented Oct 12, 2015 at 13:16

Community · Accepted Answer · 2017-05-23 12:14:31Z

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, 2017 at 12:14

CommunityBot

11 silver badge

answered Oct 12, 2015 at 21:51

Orest Hera

6,7962 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gottfried Lesigang Over a year ago

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...

Collectives™ on Stack Overflow

Parse XML field into table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related