Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • Last Modified:

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

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
Alyanto
Asked:
Alyanto
  • 6
  • 4
  • 2
  • +1
1 Solution
 
jorge_torizResearch & Development ManagerCommented:
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
 
Fernando SotoCommented:
Are you getting an exception? If so please post the exception and inner exception messages .

Which limitations are you talking about?
0
 
ArkCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
AlyantoAuthor Commented:
The code wilkl be used in an SSIS script and there is a corperate standard that excludes the use of LINQ to SQL
0
 
Fernando SotoCommented:
Can you answer the questions  that were posted to you please.
0
 
AlyantoAuthor Commented:
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
 
AlyantoAuthor Commented:
The error message is:
No mapping exists from object type System.Xml.Linq.XDocument to a known managed provider native type.
0
 
AlyantoAuthor Commented:
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
 
ArkCommented:
What SQL type is @XMLFile? If it's ntext/nvarchar/nchar etc - use
Commx.Parameters.AddWithValue("@XMLFile", doc.ToString)
0
 
AlyantoAuthor Commented:
CREATE PROCEDURE PI_InsertTransmission
  @BranchName varchar(20),
  @BranchNumber int,
  @ShortNameCode varchar(4),
  @XMLFile xml,
  @StoreItemCount decimal(18,2),
  @FileName varchar(200)
0
 
ArkCommented:
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
 
ArkCommented:
It can be
Commx.Parameters.AddWithValue("@XMLFile", doc.ToString)
or
Commx.Parameters.Add("@XMLFile",  SqlDbType.Xml).Value = doc.ToString
1
 
AlyantoAuthor Commented:
Drum roll, the award goes to Arc.  Many thanks :)

/Aly
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now