VBA to parse an XML field in SQL Server table

• kenyon1

  Valued Member

  Points: 56

  More actions

  February 23, 2018 at 10:12 am

  #403139

  All,

  I have a table in SQL Server 2016 that has columns OrderNo, LineNo, and OutputXML.  The table name is ConfigStrings

  The OutputXML is a large XML string, at the bottom of which is the data I want to display in an excel spreadsheet that I use VBA to populate.

  So here's my target data at the bottom of the XML string:

  <ProofingDetails>
    <Detail>
    <PrintSequence>10</PrintSequence>
    <PrintCode>FORMAT.LINE_BREAK</PrintCode>
    <Description>Leaf 1</Description>
    <Type>Proofing</Type>
    <DataType>String</DataType>
    <Value>GPD    PC5 RNB  F  3 0 7 0 4 3/3   CU S  G1   100   </Value>
    </Detail>
  </ProofingDetails>

  What I want to grab is just the <Value> portion of this string.  Sometimes, there will be a second leaf as well , so everything within Detail is duplicated and the Description for the 2nd one is Leaf 2.

  How do I write this?  I have plenty of experience with SQL as far as querying numbers, text, date fields, etc; but I have never done XML navigation

  So in pseudocode, what I want to do is "SELECT OrderNo, LineNo, (as many Values that exist within ProofingDetails in the OutputXML field) FROM ConfigStrings WHERE OrderNo = 'RK123'

  Can someone give me a hint?   Thank you