XML : XML Parse into Sql table - repeating values in every column

I have an xml file that I am trying to parse. However, when I parse into SQL the same value is repeated in all 7 columns and then parse looks for the next value and enters that into the next row. The code is

  DECLARE @XML AS XML, @hDoc As INT, @SQL nvarchar(MAX)    SELECT @XML = XMLData FROM XMLOpenGG    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML  SELECT GeneratedDateTime, RecordLocator, OutBound, Return, FirstName, LastName, TrackingNo, Location FROM OPENXML(@hDoc, '/CIS_PNRS/CIS_PNR/PNRDataItems/PNRDataItem')  WITH (  GeneratedDateTime varchar(50) '.',  RecordLocator varchar(50) '.',  Outbound varchar(50) '.',  Return varchar(50) '.',  FirstName varchar(100) '.',  LastName varchar(100) '.',  TrackingNo varchar(50) '.',  Location varchar(100) '.')    

The XML File structure looks like this (I have removed all values as they are sensitive);

  <?xml version="1.0"?>  <CIS_PNRS>   <CIS_PNR>    <PNRDataItems>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>     <PNRDataItem Name=""></PRNDataItem>    </PNRDataItems>   </CIS_PNR>  </CIS_PNRS>    

Any help would be so great!

No comments:

Post a Comment