Extracting data into Table using OpenXML

Question

Post reply

Extracting data into Table using OpenXML

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]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

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);

maximation Old Hand Points: 351 More actions · Answer 2

My actuall code is slightly different. The reason I need to use openXML is because I plan to automate this import as the XML content would be part of XML file and this would be part of daily feed.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

maximation (10/20/2011)
My actuall code is slightly different. The reason I need to use openXML is because I plan to automate this import as the XML content would be part of XML file and this would be part of daily feed.

Ok, well I don't see why you have to use OpenXml for that reason, but your choice.

The problem with the OpenXml solution was also in my reply - did that sort it out for you?

MM

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

maximation Old Hand Points: 351 More actions · Answer 4

MM,

Yes your solution defitnitly works and you are right about not using the OpenXML. I actually intend to insert XML data into a table and then use your query to normalize the data. so this would be the final work as it seems to work the best. Thanks alot man I really apperciate your help

INSERT tblEventCostCentersSchema(xmlCostCenter)

SELECT *FROM OPENROWSET(BULK 'D:\GSK_2011\EventCostCenters.xml',SINGLE_BLOB)

AS Z;

go

DECLARE @XmlDocument xml

SET @XmlDocument = (SELECT xmlCostCenter FROM tblEventCostCentersSchema where bitImport=0)

DECLARE @docHandle int

--@docHandle OUTPUT,

;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)