Solved

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

Posted on 2014-04-11
13
584 Views
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.
0
Comment
Question by:Alyanto
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 15

Expert Comment

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

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

0
 
LVL 63

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?
0
 
LVL 28

Expert Comment

by:Ark
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.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 1

Author Comment

by:Alyanto
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
0
 
LVL 63

Expert Comment

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

Author Comment

by:Alyanto
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.
0
 
LVL 1

Author Comment

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

Author Comment

by:Alyanto
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)

        oBranches.Add(X)

        Dim oDespatch As New Generic.List(Of Despatch)
        oDespatch.AddRange(X.DespatchList)

        Dim oStock As New Generic.List(Of StockItem)
        oStock.AddRange(X.StockList)

        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)

            Brch.Add(AttrBranchName)
            Brch.Add(AttrBranchDistrict)
            Brch.Add(AttrBranchRegion)
            Brch.Add(AttBranchZone)
            Brch.Add(AttBranchCountry)
            Brch.Add(AttBranchCountryNo)
        Next

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

        Brch.Add(Stck)
        Brch.Add(Desp)

        Dim emp = New XElement("Branches")
        emp.Add(Brch)

        doc.Add(emp)


        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
        Try
            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
                    Connx.Open()
                    Commx.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            Debug.Print(ex.Message)
        End Try




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

        'doc.Save("C:\Test\text07.xml")

    End Sub

Open in new window

0
 
LVL 28

Expert Comment

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

Author Comment

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

Accepted Solution

by:
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
0
 
LVL 28

Expert Comment

by:Ark
ID: 39998407
It can be
Commx.Parameters.AddWithValue("@XMLFile", doc.ToString)
or
Commx.Parameters.Add("@XMLFile",  SqlDbType.Xml).Value = doc.ToString
0
 
LVL 1

Author Closing Comment

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

/Aly
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 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