Passing XML multi level data as parameter and using in stored procedure

Question

I have XML format data which I will pass from a .net application.

In the SQL Server stored procedure, this data is passed in as a XML parameter. I want to read and save the data in the required tables, say TblOrder and TblItem.

In XML, there will be multiple orders. Each order contains one or several items accordingly.

Structure on which operation need to be implemented:

<?xml version="1.0" encoding="UTF-8"?>
<Orders>
  <Order>
    <B2>B2**ABIJ**0000884443**PP</B2>
    <CreateBy null="true" />
    <CreateDate>/Date(1485150414358)/</CreateDate>
    <CurrencyId>1</CurrencyId>
    <CustomerId>13</CustomerId>
    <DeliveryAddress>LIBERTY PRESS LLC</DeliveryAddress>
    <DeliveryCity>SPRINGVILLE UT 84663</DeliveryCity>
    <DeliveryCityId>0</DeliveryCityId>
    <DeliveryDate>/Date(1478750400000)/</DeliveryDate>
    <DeliveryId>14</DeliveryId>
    <DeliveryState>UT</DeliveryState>
    <DeliveryStateId>16</DeliveryStateId>
    <DeliveryType>Delivery</DeliveryType>
    <EquipmentId>4</EquipmentId>
    <Items>
      <Item>
        <CSA>false</CSA>
        <CTPAT>false</CTPAT>
        <CommodityItem>General Freight</CommodityItem>
        <CommodityItemId>0</CommodityItemId>
        <CustCommodityItem null="true" />
        <FAST>false</FAST>
        <Hazmat>false</Hazmat>
        <Height null="true" />
        <IsActive>false</IsActive>
        <ItemId>0</ItemId>
        <ItemName>Item A</ItemName>
        <Length null="true" />
        <Make null="true" />
        <Mass null="true" />
        <MassUnit null="true" />
        <Model null="true" />
        <OrderId>0</OrderId>
        <PIP>false</PIP>
        <PilotCar>false</PilotCar>
        <ReeferTemp null="true" />
        <Tarp>false</Tarp>
        <TrailerType null="true" />
        <TruckType null="true" />
        <VIN null="true" />
        <Width null="true" />
        <Year null="true" />
      </Item>
    </Items>
    <L11>L11*SYL884443*BM</L11>
    <LastUpdate>/Date(1485150414358)/</LastUpdate>
  </Order>
  <Order>
    ...
    <Items>
      <Item>
        ...
      </Item>
    </Order>
</Orders>

Steps I want to achieve are:

• Read XML parameter from the stored procedure, which is passed from the .net application.
• Loop through the XML and save data in the TblOrder and TblItem tables

Going through the article follows :

• Pass-XML-parameter-to-Stored-Procedure
• How to loop and parse xml parameter in sql server stored procedure

I got the Idea to access very first level (in my case Order of Orders). Moving forward having issue accessing the second level which will be again a collection (in my case Item of Items of Order).

Thanks in advance for your support

Answer 1

You have two approaches:

• You can pass the XML as-is into a Stored Procedure and do all the hard work in T-SQL
• You can shredd the XML within C#, fill appropriate data objects and use classical data storage.

From your question I take, that you'd prefer to pass this into a stored procedure as XML parameter. There are some things to know:

• C# uses 16-bit-unicode internally and so does SQL Server's XML. But you will not be able to cast this unicode string to XML as long as there is encoding="UTF-8" included... You might pass this as VARCHAR(MAX) (not NVARCHAR(MAX)!), but this could lead you in troubles if there are sepcial characters involved. Best was, to cut the first line (the <?xml ...?> declaration) away completely.

• Your XML is not created correctly. Is this under you control? If you include null="true" (there's no need for normally!), you should do this with the xsi-namespace. And date/time values within XML should be ISO8601. Your values (like /Date(1485150414358)/) are no format SQL Server will be able to cast directly...

Nevertheless I see multi <Order>-elements and multi <Item>-elements. You could read them as follows:

DECLARE @xml XML=
N'<Orders>
  <Order>
    <B2>B2**ABIJ**0000884443**PP</B2>
    <CreateBy null="true" />
    <CreateDate>/Date(1485150414358)/</CreateDate>
    <CurrencyId>1</CurrencyId>
    <CustomerId>13</CustomerId>
    <DeliveryAddress>LIBERTY PRESS LLC</DeliveryAddress>
    <DeliveryCity>SPRINGVILLE UT 84663</DeliveryCity>
    <DeliveryCityId>0</DeliveryCityId>
    <DeliveryDate>/Date(1478750400000)/</DeliveryDate>
    <DeliveryId>14</DeliveryId>
    <DeliveryState>UT</DeliveryState>
    <DeliveryStateId>16</DeliveryStateId>
    <DeliveryType>Delivery</DeliveryType>
    <EquipmentId>4</EquipmentId>
    <Items>
      <Item>
        <CSA>false</CSA>
        <CTPAT>false</CTPAT>
        <CommodityItem>General Freight</CommodityItem>
        <CommodityItemId>0</CommodityItemId>
        <CustCommodityItem null="true" />
        <FAST>false</FAST>
        <Hazmat>false</Hazmat>
        <Height null="true" />
        <IsActive>false</IsActive>
        <ItemId>0</ItemId>
        <ItemName>Item A</ItemName>
        <Length null="true" />
        <Make null="true" />
        <Mass null="true" />
        <MassUnit null="true" />
        <Model null="true" />
        <OrderId>0</OrderId>
        <PIP>false</PIP>
        <PilotCar>false</PilotCar>
        <ReeferTemp null="true" />
        <Tarp>false</Tarp>
        <TrailerType null="true" />
        <TruckType null="true" />
        <VIN null="true" />
        <Width null="true" />
        <Year null="true" />
      </Item>
    </Items>
    <L11>L11*SYL884443*BM</L11>
    <LastUpdate>/Date(1485150414358)/</LastUpdate>
  </Order>
</Orders>';

--the query

SELECT --elements of Order
       o.value(N'(B2)[1]',N'nvarchar(max)') AS B2

      --very strange date-format...
      ,o.value(N'(CreateDate)[1]',N'nvarchar(max)') AS CreateDate
      --typed INT
      ,o.value(N'(CurrencyId)[1]',N'int') AS CurrencyId

      --more like this

      --elements of Item
      ,i.value(N'(CSA)[1]',N'nvarchar(max)') AS CSA

      --There's no need for *null="true"*
      --Query the "/text()" and the empty element will be NULL
      ,CASE WHEN i.value(N'(CustCommodityItem/@null)[1]',N'nvarchar(max)')=N'true' THEN NULL ELSE i.value(N'(CustCommodityItem)[1]',N'nvarchar(max)') END AS CustCommodityItem_complicated
      ,i.value(N'(CustCommodityItem)[1]',N'nvarchar(max)') AS CustCommodityItem_empty
      ,i.value(N'(CustCommodityItem/text())[1]',N'nvarchar(max)') AS CustCommodityItem_null


FROM @xml.nodes(N'/Orders/Order') AS A(o)
OUTER APPLY o.nodes(N'Items/Item') AS B(i)