Extracting data into Table using OpenXML

• maximation

  Old Hand

  Points: 351

  More actions

  October 19, 2011 at 4:38 pm

  #244750

  I have a XML file which I need to extract data from. The result set should give me two rows but I'm only getting one and I'm not seeing the 2nd row which should contain the XYZ value. Can anyone tell me what I'm doing wrong? Thanks

  DECLARE @docHandle int

  DECLARE @XmlDocument nvarchar(1000)

  SET @XmlDocument = N'<?xml version="1.0" encoding="UTF-16"?>

  <EventCostCenters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"

  xmlns="http://www.tempuri.org/CostCenter.xsd">

  <TranDate>2011-03-05</TranDate>

  <CostCenters>

  <CostCenter Allocation="50">

  <JDEAccountCode>ABC.110</JDEAccountCode>

  </CostCenter>

  <CostCenter Allocation="51">

  <JDEAccountCode>XYZ.110</JDEAccountCode>

  </CostCenter>

  </CostCenters>

  </EventCostCenters>'

  EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument,

  '<CostCenters xmlns:ns1="http://www.tempuri.org/CostCenter.xsd"/>'

  SELECT * FROM-- Add OPENXML function for tblParentEvent table INSERT

  OPENXML(@docHandle, '/ns1:EventCostCenters/ns1:CostCenters',2)

  WITH

  (

  [TranDate] datetime '/ns1:EventCostCenters/ns1:TranDate',

  [Allocation] int './ns1:CostCenter/@Allocation',

  [JDEAccountCode] nvarchar(20)'./ns1:CostCenter/ns1:JDEAccountCode'

  )

  EXEC sp_xml_removedocument @docHandle

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  October 19, 2011 at 6:29 pm

  #1397029

  You are only asking for one node "CostCenters"...

  DECLARE @docHandle int

  DECLARE @XmlDocument nvarchar(1000)

  SET @XmlDocument = N'<?xml version="1.0" encoding="UTF-16"?>

  <EventCostCenters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"

  xmlns="http://www.tempuri.org/CostCenter.xsd">

  <TranDate>2011-03-05</TranDate>

  <CostCenters>

  <CostCenter Allocation="50">

  <JDEAccountCode>ABC.110</JDEAccountCode>

  </CostCenter>

  <CostCenter Allocation="51">

  <JDEAccountCode>XYZ.110</JDEAccountCode>

  </CostCenter>

  </CostCenters>

  </EventCostCenters>'

  EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument,

  '<CostCenters xmlns:ns1="http://www.tempuri.org/CostCenter.xsd"/>'

  SELECT * FROM-- Add OPENXML function for tblParentEvent table INSERT

  OPENXML(@docHandle, '/ns1:EventCostCenters/ns1:CostCenters/ns1:CostCenter',2)

  WITH

  (

  [TranDate] datetime '/ns1:EventCostCenters/ns1:TranDate',

  [Allocation] int './@Allocation',

  [JDEAccountCode] nvarchar(20)'./ns1:JDEAccountCode'

  )

  EXEC sp_xml_removedocument @docHandle

  But, I would personally recommend not using OpenXml ...

  DECLARE @XmlDocument xml

  SET @XmlDocument = N'<?xml version="1.0" encoding="UTF-16"?>

  <EventCostCenters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"

  xmlns="http://www.tempuri.org/CostCenter.xsd">

  <TranDate>2011-03-05</TranDate>

  <CostCenters>

  <CostCenter Allocation="50">

  <JDEAccountCode>ABC.110</JDEAccountCode>

  </CostCenter>

  <CostCenter Allocation="51">

  <JDEAccountCode>XYZ.110</JDEAccountCode>

  </CostCenter>

  </CostCenters>

  </EventCostCenters>'

  ;WITH xmlnamespaces ( DEFAULT 'http://www.tempuri.org/CostCenter.xsd' )

  SELECT n.value('(../../TranDate)[1]', 'datetime') AS TranDate

  , n.value('(@Allocation)[1]', 'int') AS Allocation

  , n.value('(./JDEAccountCode)[1]', 'nvarchar(20)') AS JDEAccountCode

  FROM

  @Xmldocument.nodes('EventCostCenters/CostCenters/CostCenter') X (n)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]