Read XML Input into table

• thbaig

  Hall of Fame

  Points: 3006

  More actions

  September 19, 2014 at 8:32 am

  #147056

  Hi ,

  I am using following code to read xml input to table . I am not sure what I am doing wrong, I have no error and no output

  Also can I use this select to dump data in a temp table ?

  DECLARE @testXml XML;

  select @testXml='<invoices>

  <InvoiceNo>13537010*1</InvoiceNo>

  <psship>value_psship1</psship>

  <InvoiceNo>13537010*3</InvoiceNo>

  <psship>value_psship2</psship>

  <InvoiceNo>13537010*4</InvoiceNo>

  <psship>value_psship3</psship>

  <InvoiceNo>13537010*5</InvoiceNo>

  <psship>value_psship4</psship>

  <InvoiceNo>13537010*7</InvoiceNo>

  <psship>value_psship5</psship>

  </invoices>';

  SELECT

  Tab.Col.value('@Invoiceno','varchar(20)') AS InvoiceNo,

  Tab.Col.value('@pssShip','varchar(20)') AS Shipvia

  FROM @testXml.nodes('/invoices') Tab(Col)

  Following is returning 5 rows but with NULL not real data

  DECLARE @testXml XML;

  SET @testXml='<InvoiceNo>13537010*1

  <pShip>value_pShip1</pShip>

  </InvoiceNo>

  <InvoiceNo>13537010*3

  <pShip>value_pShip2</pShip>

  </InvoiceNo>

  <InvoiceNo>13537010*4

  <pShip>value_pShip3</pShip>

  </InvoiceNo>

  <InvoiceNo>13537010*5

  <pShip>value_pShip4</pShip>

  </InvoiceNo>

  <InvoiceNo>13537010*7

  <pShip>value_pShip5</pShip>

  </InvoiceNo>';

  SELECT

  Tab.Col.value('@InvoiceNo','varchar(20)') AS InvoiceNo,

  Tab.Col.value('@psShipVia','varchar(20)') AS PShip

  FROM @testXml.nodes('/InvoiceNo') Tab(Col)

• g.britton

  SSChampion

  Points: 13863

  More actions

  September 19, 2014 at 9:32 am

  #1746759

  There's no attribute called InvoiceNo in your XML. That is, there needs to be something like this:

  <Invoice InvoiceNo="12345" />

  Gerald Britton, Pluralsight courses

• thbaig

  Hall of Fame

  Points: 3006

  More actions

  September 19, 2014 at 10:27 am

  #1746779

  thank you britton.

  Following worked. I was not aware that XML column becomes case sensitive while selecting.

  DECLARE @testXml XML;

  set @testXml=

  '<invoices>

  <InvoiceNo>13537010*1</InvoiceNo>

  <psship>value_psship1</psship>

  </invoices>

  <invoices>

  <InvoiceNo>13537010*3</InvoiceNo>

  <psship>value_psship2</psship>

  </invoices>

  ';

  SELECT

  Tab.Col.value('InvoiceNo[1]','varchar (20)') AS InvoiceNo,

  Tab.Col.value('psship[1]','varchar (20)') AS PShip

  FROM @testXml.nodes('//invoices') Tab(Col)

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  September 19, 2014 at 10:37 am

  #1746782

  Quick thought and as g.britton pointed out, the reference has to be exact, try this out for a size

  

  DECLARE @testXml XML;

  select @testXml='<invoices>

  <InvoiceNo>13537010*1</InvoiceNo>

  <psship>value_psship1</psship>

  <InvoiceNo>13537010*3</InvoiceNo>

  <psship>value_psship2</psship>

  <InvoiceNo>13537010*4</InvoiceNo>

  <psship>value_psship3</psship>

  <InvoiceNo>13537010*5</InvoiceNo>

  <psship>value_psship4</psship>

  <InvoiceNo>13537010*7</InvoiceNo>

  <psship>value_psship5</psship>

  </invoices>';

  SELECT

  INVOICE.DATA.value('.[1]','VARCHAR(50)')

  FROM @testXml.nodes('/invoices') Tab(Col)

  OUTER APPLY Tab.Col.nodes('InvoiceNo') AS INVOICE(DATA)

  Results

  -----------

  13537010*1

  13537010*3

  13537010*4

  13537010*5

  13537010*7