How to shred an xml input parameter into a table variable

Question

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

If you post code or XML, please highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Jun 16, 2010 at 16:30

marc_s · Accepted Answer · 2010-06-16 17:25:10Z

2

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??

answered Jun 16, 2010 at 17:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brian Hollister Over a year ago

aha! Another problem in addition to that was that i had the "VALUES" and not "values".......

marc_s Over a year ago

Ah, yes - those XQuery functions are indeed case-sensitive!

Collectives™ on Stack Overflow

How to shred an xml input parameter into a table variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related