How to shred an xml input parameter into a table variable

Question

Hello,

I am accepting an input parameter of xml type so that i can accept a list of insurance plans on a transaction. I need to take the xml parameter and shred it into a tale variable so that i can process some logic. How can i shred the XML into my table variable.

Here's a script to create a sample table with data in it;

/*
CREATE TABLE PlanData(PlanPos INT IDENTITY, PayerDX INT, PlanDX INT, PayPct DECIMAL(6,2))

INSERT PlanData(PayerDX, PlanDX, PayPct)
VALUES(10, 20, 80)
INSERT PlanData(PayerDX, PlanDX, PayPct)
VALUES(25, 50, 10)

drop table PlanData
*/

DECLARE @xmlPlans XML
SET @xmlPlans = (SELECT PlanPos, PayerDX, PlanDX, PayPct
                 FROM PlanData
                 ORDER BY PlanPos
                 FOR XML RAW('plan'), ROOT('plans')
                 )

print CAST(@xmlPlans AS NVARCHAR(max))

/* We must convert the XML passed in containing all of the insurance payer plan 
data into a usable format for the current scoping. */
DECLARE @tblPlans TABLE(PlanPos INT, IPDX INT, IPPDX INT, PayPct decimal(6, 2))   

--   /* Note that the table is built in order from Primary through the last plan */
--   INSERT @tblPlans (PlanPos, IPDX, IPPDX, PayPct)
--   SELECT x.item.value('@PlanPos[1]', 'INT') AS PlanPos,
--      x.item.VALUE('@IPDX[1]', 'INT') AS IPDX,
--      x.item.VALUE('@IPPDX[1]', 'INT') AS IPPDX,
--      x.item.VALUE('@PayPct[1]', 'decimal(6, 2)') AS PayPct
--   FROM @xmlPlans.nodes('//items/item') AS x(item)


   INSERT @tblPlans (PlanPos, IPDX, IPPDX, PayPct)
   SELECT T.plann.value('@PlanPos', 'int') AS PlanPos
      ,T.plann.VALUE('@PayerDX', 'INT') AS IPDX
      ,T.plann.VALUE('@PlanDX', 'INT') AS IPPDX
      ,T.plann.VALUE('@PayPct', 'decimal(6, 2)') AS PayPct
   FROM @xmlPlans.nodes('plans/plan') as T(plann)


---- Attribute-centered XML
--DECLARE @data XML
--SET @data = '<data><customer id="1" name="Allied Industries"/><customer id="2" name="Trades International"/></data>';
--
--DECLARE @tblCust TABLE(id INT, [name] VARCHAR(20))
--
--
---- Using the value() method
--INSERT @tblCust (id, [name])
--SELECT T.customer.value('@id', 'INT') AS customer_id,
--       T.customer.value('@name', 'VARCHAR(20)') AS customer_name
--FROM @data.nodes('data/customer') AS T(customer);   
--
--SELECT id AS dx, name AS CustName FROM @tblCust



--   SELECT x.item.value('@PlanPos[1]', 'INT') AS PlanPos,
--      x.item.VALUE('@IPDX[1]', 'INT') AS IPDX,
--      x.item.VALUE('@IPPDX[1]', 'INT') AS IPPDX,
--      x.item.VALUE('@PayPct[1]', 'decimal(6, 2)') AS PayPct
--   FROM @xmlPlans.nodes('//items/item') AS x

   SELECT
      p.PlanPos AS PlanPos,
      p.IPDX AS IPDX,
      p.IPPDX AS IPPDX,
      p.PayPct AS PayPct
   FROM @tblPlans p

You will also see me attempts at it that are failing.

Thank you,

Brian

Answer 1

OK, I tried your tables and I get the following output from your FOR XML RAW..... statement:

<plans>
  <plan PlanPos="1" PayerDX="10" PlanDX="20" PayPct="80.00" />
  <plan PlanPos="2" PayerDX="25" PlanDX="50" PayPct="10.00" />
</plans>

Now which of these attributes do you want to put into which of the columns of the table variable?? Your attribute names used in the x.item.value(...) statements just didn't match what is really available in the XML result from your FOR XML statement.

Try this:

DECLARE @tblPlans TABLE(PlanPos INT, IPDX INT, IPPDX INT, PayPct decimal(6, 2))   

INSERT @tblPlans (PlanPos, IPDX, IPPDX, PayPct)
  SELECT x.item.value('@PlanPos[1]', 'INT') AS PlanPos,
      x.item.value('@PayerDX[1]', 'INT') AS IPDX,
      x.item.value('@PlanDX[1]', 'INT') AS IPPDX,
      x.item.value('@PayPct[1]', 'decimal(6, 2)') AS PayPct
   FROM @xmlPlans.nodes('//plans/plan') AS x(item)

SELECT * FROM @tblPlans

I get this output from that:

PlanPos IPDX    IPPDX   PayPct
  1          10      20      80.00
  2          25      50      10.00

Or what is it that you're really looking for??