Help with OPENXML

• Nimmel

  SSC Veteran

  Points: 201

  More actions

  July 3, 2012 at 9:22 am

  #271938

  I'm posting this question here as advised from the SSIS forum (hope this is the right place):

  http://www.sqlservercentral.com/Forums/Topic1324285-148-1.aspx

  As part of an SSIS package I need to call 2 web services and then use the XML values returned to populate a table (longer description can be found in the original post). A forum member kindly advised me to try and use a stored procedure instead of manipulating the data in SSIS itself, it seems like a good solution but I have been struggling to get OPENXML working against the XML data I get returned (my SQL knowledge is basic at best).

  I hate to post a "can you fix this for me post" but I think I have exhausted all the combinations I could think of to get this working.

  I may be wrong but having looked at BOL I think I could use the OPENXML statement to achieve this. Example from BOL as below.

  DECLARE @idoc int

  DECLARE @doc varchar(1000)

  SET @doc ='

  <ROOT>

  <Customer CustomerID="VINET" ContactName="Paul Henriot">

  <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">

  <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>

  <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>

  </Order>

  </Customer>

  <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

  <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">

  <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>

  </Order>

  </Customer>

  </ROOT>'

  --Create an internal representation of the XML document.

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

  -- Execute a SELECT statement that uses the OPENXML rowset provider.

  SELECT *

  FROM OPENXML (@idoc, '/ROOT/Customer',1)

  WITH (CustomerID varchar(10),

  ContactName varchar(20))

  However I can't seem to get this to work with the XML string that the webservice I am using is providing me with, as below:

  <AllocateGiftResult xmlns:a="http://schemas.datacontract.org/2004/07/StockControlServices.Models.Response" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.xxx.co.uk/giftmanagement/"><a:AllocationReference i:nil="true" /><a:AmpsReference i:nil="true" /><a:ErrorString>StockRecordNotFound</a:ErrorString><a:ResultCode>Error</a:ResultCode></AllocateGiftResult>

  edit: XML as below as posting it as code seems to replace the <> with lt and gt.

  <AllocateGiftResult xmlns:a="http://schemas.datacontract.org/2004/07/StockControlServices.Models.Response" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.xxx.co.uk/giftmanagement/"><a:AllocationReference i:nil="true" /><a:AmpsReference i:nil="true" /><a:ErrorString>StockRecordNotFound</a:ErrorString><a:ResultCode>Error</a:ResultCode></AllocateGiftResult>

  Hopefully it is just a matter of getting the OPENXML statement correct but I have tried a fair few variations and either retrieve nulls or no values at all. Any help on how to proceed would be much appreciated.

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  July 3, 2012 at 9:33 am

  #1508294

  Try this

  DECLARE @doc XML

  SET @doc ='

  <AllocateGiftResult xmlns:a="http://schemas.datacontract.org/2004/07/StockControlServices.Models.Response"

  xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.xxx.co.uk/giftmanagement/">

  <a:AllocationReference i:nil="true" />

  <a:AmpsReference i:nil="true" />

  <a:ErrorString>StockRecordNotFound</a:ErrorString>

  <a:ResultCode>Error</a:ResultCode>

  </AllocateGiftResult>';

  WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/StockControlServices.Models.Response' AS a,

  DEFAULT 'http://www.xxx.co.uk/giftmanagement/' )

  SELECT x.r.value('(a:AllocationReference/text())[1]','VARCHAR(100)') AS AllocationReference,

  x.r.value('(a:AmpsReference/text())[1]','VARCHAR(100)') AS AmpsReference,

  x.r.value('(a:ErrorString/text())[1]','VARCHAR(100)') AS ErrorString,

  x.r.value('(a:ResultCode/text())[1]','VARCHAR(100)') AS ResultCode

  FROM @doc.nodes('/AllocateGiftResult') AS x(r);

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537