XML Query

• htc971125

  Grasshopper

  Points: 18

  More actions

  June 5, 2012 at 2:02 am

  #147344

  Hi All

  I had below XML file

  <Customer>

  <FirstName>Peter</FirstName>

  <LastName>Jones</LastName>

  <InvoiceCollection>

  <Invoice>

  <InvoiceNumber>INV001</InvoiceNumber>

  <InvoiceAmount>25</InvoiceAmount>

  <InvoiceDate>4-06-2012</InvoiceDate>

  <InvoiceLineCollection>

  <InvoiceLine>

  <LineNumber>1</LineNumber>

  <ChargeDescription>Apple</ChargeDescription>

  <LineAmount>10</LineAmount>

  </InvoiceLine>

  <InvoiceLine>

  <LineNumber>2</LineNumber>

  <ChargeDescription>Orange</ChargeDescription>

  <LineAmount>15</LineAmount>

  </InvoiceLine>

  </InvoiceLineCollection>

  </Invoice>

  <Invoice>

  <InvoiceNumber>INV002</InvoiceNumber>

  <InvoiceAmount>22</InvoiceAmount>

  <InvoiceDate>5-06-2012</InvoiceDate>

  <InvoiceLineCollection>

  <InvoiceLine>

  <LineNumber>1</LineNumber>

  <ChargeDescription>Grapes</ChargeDescription>

  <LineAmount>7</LineAmount>

  </InvoiceLine>

  <InvoiceLine>

  <LineNumber>2</LineNumber>

  <ChargeDescription>BlueBerry</ChargeDescription>

  <LineAmount>15</LineAmount>

  </InvoiceLine>

  </InvoiceLineCollection>

  </Invoice>

  </InvoiceCollection>

  </Customer>

  I would like to query it so it display

  FirstName LastName InvoiceNo InvoiceAmtInvoiceDateLineNoChargeDescLineQAmt

  PeterJonesINV001254-06-20121Apple10

  PeterJonesINV001254-06-20122Orange15

  PeterJonesINV002224-06-20121Grapes7

  PeterJonesINV002224-06-20122BlueBerry 15

  I Tried

  SELECT

  T2.Line.value('(..../FirstName/text())[1]','VARCHAR(30)') as FirstName,

  T2.Line.value('(..../LastName/text())[1]','VARCHAR(30)') as LastName,

  T2.Line.value('(../InvoiceNumber/text())[1]','VARCHAR(30)') as InvoiceNumber,

  T2.Line.value('(../InvoiceAmount/text())[1]','INT') as InvoiceAmount,

  T2.Line.value('(../InvoiceDate/text())[1]','Datetime)') as InvoiceDate,

  T2.Line.value('(LineNumber/text())[1]','INT') as LineNumber,

  T2.Line.value('(ChargeDescription/text())[1]','VARCHAR(30)') as ChargeDescription,

  T2.Line.value('(LineAmount/text())[1]','INT') as LineAmount

  FROM #xml_data

  CROSS APPLY data.nodes('/Customer/InvoiceCollection/Invoice/InvoiceLineCollection/InvoiceLine') as T2(Line)

  But errors me

  Can anyone help to provide the correct query

  Many thanks in advance

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  June 6, 2012 at 9:06 am

  #1497686

  SELECT

  T2.Line.value('(../../../../FirstName/text())[1]','VARCHAR(30)') as FirstName,

  T2.Line.value('(../../../../LastName/text())[1]','VARCHAR(30)') as LastName,

  T2.Line.value('(../../InvoiceNumber/text())[1]','VARCHAR(30)') as InvoiceNumber,

  T2.Line.value('(../../InvoiceAmount/text())[1]','INT') as InvoiceAmount,

  CAST(T2.Line.value('(../../InvoiceDate/text())[1]','VARCHAR(30)') AS DATETIME) as InvoiceDate,

  T2.Line.value('(LineNumber/text())[1]','INT') as LineNumber,

  T2.Line.value('(ChargeDescription/text())[1]','VARCHAR(30)') as ChargeDescription,

  T2.Line.value('(LineAmount/text())[1]','INT') as LineAmount

  FROM #xml_data

  CROSS APPLY data.nodes('/Customer/InvoiceCollection/Invoice/InvoiceLineCollection/InvoiceLine') as T2(Line)

  Please next time provide DDL and test data setup as per link at the bottom of my signature...

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• htc971125

  Grasshopper

  Points: 18

  More actions

  June 6, 2012 at 5:46 pm

  #1497961

  Thanks you very much