XML Structure

• dwomack

  Grasshopper

  Points: 10

  More actions

  March 27, 2013 at 10:04 am

  #74174

  I am trying to create xml from SQL that looks like what’s below.I am using Sql Server 2008 R2. Everything is working Correctly but the Bolded tags. I can’t seem to figure out how to get those in there. I am using FOR XML AUTO.

  <Records>

  <NewLoan>

  <AppraisalValue>350000</AppraisalValue>

  <AppraisalDate>4/18/2006</AppraisalDate>

  <AdditionalBorrowers>

  <AdditionalBorrower>

  <BorrowerNumber>1</BorrowerNumber>

  <AddBorrowerFirstName>Mickey</AddBorrowerFirstName>

  <AddBorrowerLastName>Mouse</AddBorrowerLastName>

  <AddBorrowerMiddleInitial>M</AddBorrowerMiddleInitial>

  <AddBorrowerSSN>123456789</AddBorrowerSSN>

  <AddBorrowerStreet>123 Main Street</AddBorrowerStreet>

  <AddBorrowerCity>City Name</AddBorrowerCity>

  <AddBorrowerState>NJ</AddBorrowerState>

  <AddBorrowerZIP>08618</AddBorrowerZIP>

  <AddBorrowerForeignAddress>N</AddBorrowerForeignAddress>

  <AddBorrowerTelephone>1234567890</AddBorrowerTelephone>

  </AdditionalBorrower>

  <AdditionalBorrower>

  <BorrowerNumber>2</BorrowerNumber>

  <AddBorrowerFirstName>Minnie</AddBorrowerFirstName>

  <AddBorrowerLastName>Mouse</AddBorrowerLastName>

  <AddBorrowerMiddleInitial>M</AddBorrowerMiddleInitial>

  <AddBorrowerSSN>123456780</AddBorrowerSSN>

  <AddBorrowerStreet>125 Main Street</AddBorrowerStreet>

  <AddBorrowerCity>City Name, England</AddBorrowerCity>

  <AddBorrowerState></AddBorrowerState>

  <AddBorrowerZIP>D5SF2</AddBorrowerZIP>

  <AddBorrowerForeignAddress>Y</AddBorrowerForeignAddress>

  <AddBorrowerTelephone>1234567891</AddBorrowerTelephone>

  </AdditionalBorrower>

  </AdditionalBorrowers>

  <FloodCoCode>FRA5A</FloodCoCode>

  <FloodProgram>R</FloodProgram>

  </NewLoan>

  </Records>

  My Query Looks like…

  Select Distinct

  NewLoan.AppraisalValue, NewLoan.AppraisalDate,

  AdditionalBorrower.BorrowerNumber, AdditionalBorrower.AddBorrowerFirstName, AdditionalBorrower.AddBorrowerMiddleName,AdditionalBorrower.AddBorrowerLastName, AdditionalBorrower.AddBorrowerSSN,

  AdditionalBorrower.AddBorrowerStreet, AdditionalBorrower.AddBorrowerCity, AdditionalBorrower.AddBorrowerState, AdditionalBorrower.AddBorrowerZip,

  AdditionalBorrower.AddBorrowerTelephone

  FROM NewLoan LEFT OUTER JOIN

  AdditionalBorrower ON NewLoan.loanGeneral_Id = AdditionalBorrower.loanGeneral_Id

  for xml auto, ROOT ('RECORDS'), Elements ;

  Thank you for any help

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  March 27, 2013 at 10:21 am

  #1601247

  Pretty sure you cannot do this using XML Auto.

  Try Using XML Path

  Here's an example using Path from AdventureWorks

  SELECT

  h.SalesOrderID,

  h.OrderDate,

  h.DueDate,

  h.ShipDate,

  h.Status,

  h.SubTotal,

  h.TaxAmt,

  h.Freight,

  h.TotalDue,

  (SELECT

  CarrierTrackingNumber,

  OrderQty,

  ProductID,

  UnitPrice,

  UnitPriceDiscount,

  LineTotal,

  ModifiedDate

  FROM [Sales].[SalesOrderDetail] d

  WHERE h.SalesOrderID = d.SalesOrderID

  FOR XML PATH('OrderDetail'), ROOT('OrderDetails'), Type)

  FROM [Sales].[SalesOrderHeader] h

  WHERE h.SalesOrderID = 43666

  FOR XML PATH('SalesOrder'), ROOT('SalesOrders')

  <SalesOrders>

  <SalesOrder>

  <SalesOrderID>43666</SalesOrderID>

  <OrderDate>2001-07-01T00:00:00</OrderDate>

  <DueDate>2001-07-13T00:00:00</DueDate>

  <ShipDate>2001-07-08T00:00:00</ShipDate>

  <Status>5</Status>

  <SubTotal>6079.6842</SubTotal>

  <TaxAmt>486.3747</TaxAmt>

  <Freight>151.9921</Freight>

  <TotalDue>6718.0510</TotalDue>

  <OrderDetails>

  <OrderDetail>

  <CarrierTrackingNumber>D46A-40CA-8D</CarrierTrackingNumber>

  <OrderQty>1</OrderQty>

  <ProductID>764</ProductID>

  <UnitPrice>419.4589</UnitPrice>

  <UnitPriceDiscount>0.0000</UnitPriceDiscount>

  <LineTotal>419.458900</LineTotal>

  <ModifiedDate>2001-07-01T00:00:00</ModifiedDate>

  </OrderDetail>

  <OrderDetail>

  <CarrierTrackingNumber>D46A-40CA-8D</CarrierTrackingNumber>

  <OrderQty>1</OrderQty>

  <ProductID>753</ProductID>

  <UnitPrice>2146.9620</UnitPrice>

  <UnitPriceDiscount>0.0000</UnitPriceDiscount>

  <LineTotal>2146.962000</LineTotal>

  <ModifiedDate>2001-07-01T00:00:00</ModifiedDate>

  </OrderDetail>

  </OrderDetails>

  </SalesOrder>

  </SalesOrders>

• dwomack

  Grasshopper

  Points: 10

  More actions

  March 27, 2013 at 12:30 pm

  #1601309

  Perfect! Thank you so much