XML into SQL Server 2008 R2 Table from VB via Stored Procedure

Posted on 2014-04-11
Last Modified: 2014-04-14
I have an XML document that I want to post into a SQL Server 2008 R2 Table from VB via Stored Procedure.  The Proc has a parameter field of type XML.  

Commx.Parameters.AddWithValue("@XMLFile", doc)

Open in new window

I use this line where Commx is a declared as
 Using Commx As New SqlCommand

Open in new window

The limitations I have is that I can't use LINQ to SQL in this case.
Question by:Alyanto
  • 6
  • 4
  • 2
  • +1
LVL 15

Expert Comment

ID: 39994918
Is you want to convert XML to a table you can use a combination of .nodes and .value functions.   For example:
   <product id="1" name="Product 1" />
   <product id="2" name="Product 2" />
   <product id="3" name="Product 3" />
   <product id="4" name="Product 4" />

Open in new window

Then you can get the info with the following code:
SELECT c.value('@id', 'TINYINT') AS Id, c.value('@name', 'VARCHAR(9)') AS Name
FROM @YourXMLParam.nodes('/products/product') AS T(c)

Open in new window

LVL 62

Expert Comment

by:Fernando Soto
ID: 39995065
Are you getting an exception? If so please post the exception and inner exception messages .

Which limitations are you talking about?
LVL 27

Expert Comment

ID: 39995592
What is a problem with LINQ?
Drag your procedure to the data context designer and it'll generate a method for you. Just call it with your xml as argument.

Author Comment

ID: 39995754
The code wilkl be used in an SSIS script and there is a corperate standard that excludes the use of LINQ to SQL
LVL 62

Expert Comment

by:Fernando Soto
ID: 39995910
Can you answer the questions  that were posted to you please.

Author Comment

ID: 39995928
I will when I return to work on Monday provide it exactly however the sentiment of the message was that no mapping of type Linq,XML was possible with the parameter.  I did check to see if the Stored Proc's parameter was of the correct name and type XML and they were as expected.
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!


Author Comment

ID: 39998375
The error message is:
No mapping exists from object type System.Xml.Linq.XDocument to a known managed provider native type.

Author Comment

ID: 39998377
My code base, Still needs a a tidy and optimization but works other than the save  to database action

Public Class Branches
    Public Sub New(ByVal BranchNumber As Integer)

        Dim X As New Branch(24)

        Dim oBranches As New Generic.List(Of Branch)


        Dim oDespatch As New Generic.List(Of Despatch)

        Dim oStock As New Generic.List(Of StockItem)

        Dim doc = New XDocument()

        Dim Brch As XElement = New XElement("Branch", From b As Branch In oBranches _
                                                      Select New XAttribute("BranchNumber", b.BranchNumber))

        Dim BranchName As String = String.Empty
        Dim ShortNameCode As String = String.Empty
        Dim StoreItemCount As Decimal = oBranches.Count + oDespatch.Count

        For Each b As Branch In oBranches
            BranchName = b.BranchName
            ShortNameCode = b.ShortNameCode

            Dim AttrBranchName As XAttribute = New XAttribute("BranchName", b.BranchName)
            Dim AttrBranchDistrict As XAttribute = New XAttribute("BranchDistrict", b.BranchDistrict)
            Dim AttrBranchRegion As XAttribute = New XAttribute("BranchRegion", b.BranchRegion)
            Dim AttBranchZone As XAttribute = New XAttribute("BranchZone", b.BranchZone)
            Dim AttBranchCountry As XAttribute = New XAttribute("BranchCountry", b.BranchCountry)
            Dim AttBranchCountryNo As XAttribute = New XAttribute("BranchCountryNo", b.BranchCountryNo)


        Dim Stck As XElement = New XElement("Stock", From s As StockItem In oStock _
                                                     Select New XElement("Item", _
                                                                         New XAttribute("LongCode", s.LongCode), _
                                                                         New XAttribute("ShortCode", s.ShortCode), _
                                                                         New XAttribute("ProductDescription", s.ProductDescription), _
                                                                         New XAttribute("BranchSellingPrice", s.BranchSellingPrice), _
                                                                         New XAttribute("CurrentStockUnits", s.CurrentStockUnits), _
                                                                         New XAttribute("ToPickUnits", s.ToPickUnits), _
                                                                         New XAttribute("AnalysisCode1", s.AnalysisCode1), _
                                                                         New XAttribute("Analysiscode1Description", s.Analysiscode1Description), _
                                                                         New XAttribute("AnalysisCode2", s.AnalysisCode2), _
                                                                         New XAttribute("AnalysisCode2Description", s.AnalysisCode2Description), _
                                                                         New XAttribute("CountryAnalysisCode", s.CountryAnalysisCode), _
                                                                         New XAttribute("CountryAnalysisCodeDescription", s.CountryAnalysisCodeDescription), _
                                                                         New XAttribute("StockHoldingGroup", s.StockHoldingGroup), _
                                                                         New XAttribute("StockHoldingGroupDescription", s.StockHoldingGroupDescription), _
                                                                         New XAttribute("DepartmentName", s.DepartmentName), _
                                                                         New XAttribute("DepartmentNumber", s.DepartmentNumber), _
                                                                         New XAttribute("ClassNumber", s.ClassNumber), _
                                                                         New XAttribute("ClassName", s.ClassName)))

        Dim Desp As XElement = New XElement("Despatches", From d As Despatch In oDespatch _
                                                        Select New XElement("Despatch", _
                                                                            New XAttribute("LongCode", d.LongCode), _
                                                                            New XAttribute("Units", d.Units), _
                                                                            New XAttribute("TransactionType", d.TransactionType), _
                                                                            New XAttribute("Memo", d.Memo), _
                                                                            New XAttribute("OtherBranchNumber", d.OtherBranchNumber), _
                                                                            New XAttribute("TransactionDate", d.TransactionDate)))


        Dim emp = New XElement("Branches")


        Dim FileName As String = String.Empty

        FileName = String.Format("{0}{1}_{2}.XML", BranchNumber.ToString.PadLeft(4, "0"), BranchName.Replace(" ", ""), Date.Today.ToShortDateString.Replace("/", ""))

        'Dim Dt As New DataTable
        'Dim dtAdapter As SqlDataAdapter
            Using Connx As New SqlConnection("Server=Dashboard;Database=PASTIS;Trusted_Connection=True;")
                Using Commx As New SqlCommand
                    Commx.CommandType = CommandType.StoredProcedure
                    Commx.CommandText = "PI_InsertTransmission"
                    Commx.Parameters.AddWithValue("@BranchName", BranchName)
                    Commx.Parameters.AddWithValue("@BranchNumber", BranchNumber)
                    Commx.Parameters.AddWithValue("@ShortNameCode", ShortNameCode)
                    Commx.Parameters.AddWithValue("@XMLFile", doc)
                    Commx.Parameters.AddWithValue("@StoreItemCount", StoreItemCount)
                    Commx.Parameters.AddWithValue("@FileName", FileName)
                    Commx.Connection = Connx
                End Using
            End Using
        Catch ex As Exception
        End Try

        'PI_InsertTransmission @BranchName varchar(20),@BranchNumber int, @ShortNameCode varchar(4), @XMLFile xml, @StoreItemCount decimal(18,2),@FileName varchar(200)


    End Sub

Open in new window

LVL 27

Expert Comment

ID: 39998381
What SQL type is @XMLFile? If it's ntext/nvarchar/nchar etc - use
Commx.Parameters.AddWithValue("@XMLFile", doc.ToString)

Author Comment

ID: 39998386
CREATE PROCEDURE PI_InsertTransmission
  @BranchName varchar(20),
  @BranchNumber int,
  @ShortNameCode varchar(4),
  @XMLFile xml,
  @StoreItemCount decimal(18,2),
  @FileName varchar(200)
LVL 27

Accepted Solution

Ark earned 500 total points
ID: 39998399
According to MSDN
For SqlDbType Xml enumeration values, you can use a string, an XML value, an XmlReader derived type instance, or a SqlXml object.
So you can use doc.ToString
LVL 27

Expert Comment

ID: 39998407
It can be
Commx.Parameters.AddWithValue("@XMLFile", doc.ToString)
Commx.Parameters.Add("@XMLFile",  SqlDbType.Xml).Value = doc.ToString

Author Closing Comment

ID: 39998415
Drum roll, the award goes to Arc.  Many thanks :)


Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now