Solved

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

Posted on 2014-04-11
13
573 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 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?
0
 
LVL 27

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

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 27

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 27

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 27

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

19 Experts available now in Live!

Get 1:1 Help Now