Create and fill table in SQL server from XML file

<?xml version="1.0" encoding="UTF-8" ?>

<Dealers>

  <Dealer id='77'>

    <Dealer_Address>

      <Name>Action Kia</Name>

      <Address>Cnr Bruce Highway And Oak St</Address>

      <Suburb>Gympie</Suburb>

      <State>QLD</State>

      <Postcode>4570</Postcode>

      <WorkPhone>(07) 5482 2759</WorkPhone>

      <Opening_Hour>Mon-Fri: 8:00 AM-6:00 PM-br-Sat: 8:00 AM-12:00 PM-br-Sun: Closed</Opening_Hour>

      <Fax>(07) 5482 2389</Fax>

      <Url>www.actionautogroup.com.au</Url>

      <Email></Email>

      <Longitude>152.650454</Longitude>

      <Latitude>-26.179577</Latitude>

    </Dealer_Address>

    <Service_Address>

      <Name>Action Kia</Name>

      <Address>Cnr Bruce Highway And Oak St</Address>

      <Suburb>Gympie</Suburb>

      <State>QLD</State>

      <Postcode>4570</Postcode>

      <WorkPhone>(07) 5482 2759</WorkPhone>

      <Opening_Hour>Mon-Fri: 8:00 AM-6:00 PM-br-Sat: 8:00 AM-12:00 PM-br-Sun: Closed</Opening_Hour>

    </Service_Address>

  </Dealer>

  <Dealer id='7'>

    <Dealer_Address>

      <Name>Adtrans Kia</Name>

      <Address>1178 - 1184 South Road</Address>

      <Suburb>Clovelly Park</Suburb>

      <State>SA</State>

      <Postcode>5042</Postcode>

      <WorkPhone>(08) 8374 2744</WorkPhone>

      <Opening_Hour></Opening_Hour>

      <Fax>(08) 8374 2755</Fax>

      <Url>www.adtranskia.com.au</Url>

      <Email></Email>

      <Longitude>138.574837</Longitude>

      <Latitude>-34.997404</Latitude>

    </Dealer_Address>

    <Service_Address>

      <Name>Adtrans Kia</Name>

      <Address>1178 - 1184 South Road</Address>

      <Suburb>Clovelly Park</Suburb>

      <State>SA</State>

      <Postcode>5042</Postcode>

      <WorkPhone>(08) 8374 2744</WorkPhone>

      <Opening_Hour></Opening_Hour>

    </Service_Address>

  </Dealer>

</Dealers>

SELECT

      product.value('../@id', 'int') as id,

      replace(X.product.query('Name').value('.', 'VARCHAR(300)'), 'And', '&') as Name,

      X.product.query('Address').value('.', 'VARCHAR(300)')as Address,

      X.product.query('Suburb').value('.', 'VARCHAR(300)')as Suburb,

      X.product.query('State').value('.', 'VARCHAR(300)') as State,

      X.product.query('Postcode').value('.', 'VARCHAR(30)') as Postcode,

      X.product.query('WorkPhone').value('.', 'VARCHAR(20)') as WorkPhone,

      replace(X.product.query('Opening_Hour').value('.', 'VARCHAR(300)'), '-br','<br>') as Opening_Hour

INTO Service_Address

FROM

(

      SELECT CAST(x AS XML) FROM OPENROWSET(BULK 'D:\dealers_2012125 - Copy.xml', SINGLE_BLOB) AS T(x)

) AS T(x)

CROSS APPLY x.nodes('Dealers/Dealer/Service_Address') AS X(product);