SQL XML XSD Experts for Foreign Key pass down issue

Hi Experts,

I have a hard one here.

I have an XSD file, all marked up for SQL/ annotated.

I want to use Bulk Upload to send it up, but I keep finding errors.
I have tried many combinations of the sql:relationship/ sql:relation and the current error I am getting is "Schema: relationship expected on 'Zone'."

I clearly have a relationship for this field.

The main issue is that when I have multiple "Voltage" children, the Db does not fill at all, and I get no errors at all.

See XSD, XML and Code attached.
Also the SQL Table Structure is attached.

Thanks
Private Sub butBulkLoadXMLFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles butBulkLoadXMLFile.Click

        If oDB._strConnString = "" Then
            oDB._strConnString = "Provider=SQLNCLI;Data Source=xxx;Initial Catalog=xxxx;User Id=xxx;Password=xxxx;"
            oDB._strErrorLogPath = "c:\temp\"
            oDB._strTempDirONSQL = "\\dev\temp\XML_Vemco\"
        End If

        Try

            Dim objBL As SQLXMLBULKLOADLib.SQLXMLBulkLoad4 = New SQLXMLBULKLOADLib.SQLXMLBulkLoad4

            objBL.ConnectionString = oDB._strConnString
            objBL.ErrorLogFile = oDB._strErrorLogPath & "error" & Format(Now(), "hh_ss_dd_mm_yyyy") & ".log"
            objBL.CheckConstraints = True

            objBL.Transaction = True

            'objBL.SchemaGen = True
            'objBL.SGDropTables = False
            'objBL.SGUseID = False

            objBL.BulkLoad = True
            objBL.KeepIdentity = False
            objBL.TempFilePath = oDB._strTempDirONSQL


            Debug.Print("XSD Path:" & Me.txtSQLXSDPath.Text.ToString)
            Debug.Print("XML Path:" & Me.txtXMLPath.Text.ToString)
            Debug.Print("Server:" & oDB._strConnString)

            objBL.Execute(Me.txtSQLXSDPath.Text.ToString, Me.txtXMLPath.Text.ToString)

        Catch ex As Exception
            Debug.Print(Now() & ":" & ex.Message)
        End Try

    End Sub

Open in new window

forExperts-Voltage.xsd
VMS-IWS-VEMCO-WP-20100315120500-.xml
forExperts.sql
LVL 1
Craig LambieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BanthorCommented:
Why mark it up with the XSD. Why not load the XML into a stored procedure and process there.
0
Craig LambieAuthor Commented:
Banthor, I could do that, but I thought this was the easiest way to map fields from XML to a relational Db schema.

Do expand on this thought...
I asked the question... What is the best way to do this, and got zero response... so maybe you have that answer..
0
BanthorCommented:
So this is as far as I can go on this, I use XML a lot for storing Web pieces and documents and searching for exists.
Not so much for returning recordsets. for some reason I only get the first Voltage value rather than a set of 2 values.
I had to remove the first element completely to get any valid results.
<InspectionWorkStatus xmlns="http://www.xxx.com.au/vms/v0.5">
So you may want to re-evaluate the XSD

 



Declare @PAth varchar(255), @Filename varchar(200)
Select @PAth='\\seo\DataDrive\DevBin\XML', @Filename='VMS-IWS-VEMCO-WP-20100315120500-.xml'
Declare @Src varchar(max)
SELECT @Src = coalesce(@Src,'')+[line] FROM [dbo].[udf_fsoReadfileAsTable] (
   @PAth
  ,@Filename)

Declare @XML XML

Select @XML = Cast(@SRC as XML)
Select @XML

DECLARE @idoc int
DECLARE @doc varchar(max)

SET @doc ='  <root>
  <vchFIWP_ReferenceID>20100331110116_Voltage</vchFIWP_ReferenceID>
  <vchFIWP_FilePath>xxxxxx</vchFIWP_FilePath>
  <vchFIWP_FileNameFromWP>xxxxxx</vchFIWP_FileNameFromWP>
  <dtFIWP_FileReceived>2010-04-27 03:03:44</dtFIWP_FileReceived>
  <dtFIWP_Validated>2010-04-27 03:03:44</dtFIWP_Validated>
  <chFIWP_PackageType>IWS</chFIWP_PackageType>
  <intFIWP_SpanCount>175</intFIWP_SpanCount>
  <Contractor>xxx</Contractor>
  <Date>2010-03-15T10:43:06.000+10:00</Date>
  <VegetationYear>2009</VegetationYear>
  <Zone>
    <FireRisk>M</FireRisk>
    <VegetationZone>EC</VegetationZone>
    <MaintenanceZone>NOR/Z121</MaintenanceZone>
  </Zone>
  <Spans>
    <Span refID="xxx">
      <ToPole>
        <PickID>xxx</PickID>
        <Location>9xxx</Location>
        <LocationName>GO90/67/15/12</LocationName>
        <Substation>NORTHAM</Substation>
        <Feeder>NOR 502.0 GOOMALLING</Feeder>
        <District>xxx</District>
        <LocalGovernmentAuthority>xxx</LocalGovernmentAuthority>
        <Address>
          <Street>xxx</Street>
          <Suburb>xxx</Suburb>
        </Address>
        <SlotType>xxx</SlotType>
        <DropOutFuseFitted>false</DropOutFuseFitted>
        <Mains>
          <Voltage>HV</Voltage>
          <Voltage>SM</Voltage>
        </Mains>
        <Longitude>-3333</Longitude>
        <Latitude>333333</Latitude>
      </ToPole>
      <InspectionDetails>
        <Date>2010-01-14T10:19:12.000+10:00</Date>
        <Contractor>xxxx</Contractor>
        <Name>bh</Name>
        <DistributionCode>NVS</DistributionCode>
      </InspectionDetails>
    </Span>
  </Spans>
  </root>'

SElect @Src=@doc

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @SRC
-- SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/root/Spans/Span/ToPole/Mains',2)
			with(Voltage varchar(100))
			
EXEC sp_xml_removedocument @idoc

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Craig LambieAuthor Commented:
Ok, that looks great.

How, without knowing the exact layout of the XML, do I now get it into my tables?

0
Anthony PerkinsCommented:
Just a slight correction to the previous solution (this includes using the namespace and the correct XPath to all the Voltage values):
DECLARE @idoc int
DECLARE @doc varchar(max)

SET @doc = 
'<InspectionWorkStatus xmlns="http://www.xxx.com.au/vms/v0.5">
	<vchFIWP_ReferenceID>20100331110116_Voltage</vchFIWP_ReferenceID>
	<vchFIWP_FilePath>xxxxxx</vchFIWP_FilePath>
	<vchFIWP_FileNameFromWP>xxxxxx</vchFIWP_FileNameFromWP>
	<dtFIWP_FileReceived>2010-04-27 03:03:44</dtFIWP_FileReceived>
	<dtFIWP_Validated>2010-04-27 03:03:44</dtFIWP_Validated>
	<chFIWP_PackageType>IWS</chFIWP_PackageType>
	<intFIWP_SpanCount>175</intFIWP_SpanCount>
	<Contractor>xxx</Contractor>
	<Date>2010-03-15T10:43:06.000+10:00</Date>
	<VegetationYear>2009</VegetationYear>
	<Zone>
		<FireRisk>M</FireRisk>
		<VegetationZone>EC</VegetationZone>
		<MaintenanceZone>NOR/Z121</MaintenanceZone>
	</Zone>
	<Spans>
		<Span refID="xxx">
			<ToPole>
				<PickID>xxx</PickID>
				<Location>9xxx</Location>
				<LocationName>GO90/67/15/12</LocationName>
				<Substation>NORTHAM</Substation>
				<Feeder>NOR 502.0 GOOMALLING</Feeder>
				<District>xxx</District>
				<LocalGovernmentAuthority>xxx</LocalGovernmentAuthority>
				<Address>
					<Street>xxx</Street>
					<Suburb>xxx</Suburb>
				</Address>
				<SlotType>xxx</SlotType>
				<DropOutFuseFitted>false</DropOutFuseFitted>
				<Mains>
					<Voltage>HV</Voltage>
					<Voltage>SM</Voltage>
				</Mains>
				<Longitude>-3333</Longitude>
				<Latitude>333333</Latitude>
			</ToPole>
			<InspectionDetails>
				<Date>2010-01-14T10:19:12.000+10:00</Date>
				<Contractor>xxxx</Contractor>
				<Name>bh</Name>
				<DistributionCode>NVS</DistributionCode>
			</InspectionDetails>
		</Span>
	</Spans>
</InspectionWorkStatus>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<root xmlns:ns="http://www.xxx.com.au/vms/v0.5"/>'

SELECT  Voltage
FROM    OPENXML (@idoc, 'ns:InspectionWorkStatus/ns:Spans/ns:Span/ns:ToPole/ns:Mains/ns:Voltage', 2) WITH (
			Voltage varchar(10) '.')
			
EXEC sp_xml_removedocument @idoc

Open in new window

0
Anthony PerkinsCommented:
For a more modern and simpler approach consider using the new (if you can consider SQL Server 2005 as new) Xml Data Type Methods.  As in:
DECLARE @idoc int
DECLARE @doc Xml

SET @doc = 
'<InspectionWorkStatus xmlns="http://www.xxx.com.au/vms/v0.5">
	<vchFIWP_ReferenceID>20100331110116_Voltage</vchFIWP_ReferenceID>
	<vchFIWP_FilePath>xxxxxx</vchFIWP_FilePath>
	<vchFIWP_FileNameFromWP>xxxxxx</vchFIWP_FileNameFromWP>
	<dtFIWP_FileReceived>2010-04-27 03:03:44</dtFIWP_FileReceived>
	<dtFIWP_Validated>2010-04-27 03:03:44</dtFIWP_Validated>
	<chFIWP_PackageType>IWS</chFIWP_PackageType>
	<intFIWP_SpanCount>175</intFIWP_SpanCount>
	<Contractor>xxx</Contractor>
	<Date>2010-03-15T10:43:06.000+10:00</Date>
	<VegetationYear>2009</VegetationYear>
	<Zone>
		<FireRisk>M</FireRisk>
		<VegetationZone>EC</VegetationZone>
		<MaintenanceZone>NOR/Z121</MaintenanceZone>
	</Zone>
	<Spans>
		<Span refID="xxx">
			<ToPole>
				<PickID>xxx</PickID>
				<Location>9xxx</Location>
				<LocationName>GO90/67/15/12</LocationName>
				<Substation>NORTHAM</Substation>
				<Feeder>NOR 502.0 GOOMALLING</Feeder>
				<District>xxx</District>
				<LocalGovernmentAuthority>xxx</LocalGovernmentAuthority>
				<Address>
					<Street>xxx</Street>
					<Suburb>xxx</Suburb>
				</Address>
				<SlotType>xxx</SlotType>
				<DropOutFuseFitted>false</DropOutFuseFitted>
				<Mains>
					<Voltage>HV</Voltage>
					<Voltage>SM</Voltage>
				</Mains>
				<Longitude>-3333</Longitude>
				<Latitude>333333</Latitude>
			</ToPole>
			<InspectionDetails>
				<Date>2010-01-14T10:19:12.000+10:00</Date>
				<Contractor>xxxx</Contractor>
				<Name>bh</Name>
				<DistributionCode>NVS</DistributionCode>
			</InspectionDetails>
		</Span>
	</Spans>
</InspectionWorkStatus>'

; WITH XMLNAMESPACES ('http://www.xxx.com.au/vms/v0.5' AS ns)
SELECT  T.c.value('.', 'varchar(50)' ) Voltage
FROM    @doc.nodes ('ns:InspectionWorkStatus/ns:Spans/ns:Span/ns:ToPole/ns:Mains/ns:Voltage') T(c)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Craig LambieAuthor Commented:
acperkins, thanks so much.

But I still don't get how to put the XML data into table's using the XSD or some other method of mapping the fields to a SQL Schema in a couple of simple steps.

I have been trying to use this http://msdn.microsoft.com/en-us/library/ms171993.aspx SqlBulkLoad (I am operating in SQL 2005 env btw) but it is prone to errors, and I thought dealing with the mapping on the SQL Server might work well.

Thoughts?
0
Anthony PerkinsCommented:
I understand exactly what you are trying to do and why you are using xsd, unfortunately I have no experience whatsoever using SQLBulkLoad.  I was merely expanding on Banthor's approach using OPENXML.

Good luck.
0
Craig LambieAuthor Commented:
Thanks acperkins....

Given your knowledge of OpenXML in T-SQL maybe there is a way to easily map fields from an XML file to the SQL server?
That is / was my original question.  If using BulkLoad is the wrong way of doing it, then please enlighten me.  Maybe there is a way to move an XML file into Tables in T-SQL?
0
Anthony PerkinsCommented:
>>Given your knowledge of OpenXML in T-SQL maybe there is a way to easily map fields from an XML file to the SQL server?<<
Yes, there is.  However, it does require some application to read the files and pass them as a stream, similar to the examples posted here.

>>If using BulkLoad is the wrong way of doing it, then please enlighten me.<<
I did not say that.  I am sure it is a good approach, I simply have never used it.  It certainly solves the problem of using files intead of their contents (like OPENXML and the Xml Data Type Methods)

>> Maybe there is a way to move an XML file into Tables in T-SQL?<<
There are many ways, we have just talked about two methods.  The only one I know well, is using OPENXML() or the Xml Data Type Methods.  But you will have to write a small app using (VB6, VBScript, .NET, etc.) to open the file and pass in the contents so that SQL can shred it.
0
Craig LambieAuthor Commented:
getting the XML into SQL server is the easy part.

Shredding it and mapping it to fields is the part I am struggling with to be honest.

The examples above will allow you to xpath into the XML, I want to map the Xpath's to table.field, how do you do that in a few simple steps? If possible?
BulkLoad is great, but is prone to errors which are not particularily descriptive, so I am happy to assess another way... OPENXML sounds good.
0
Anthony PerkinsCommented:
>>Shredding it and mapping it to fields is the part I am struggling with to be honest.<<
I can definitely help with that.  I know you attached some files, but if you can simply state how the nodes in the Xml are mapped to which table column, I can then see if I can come up with a solution.
0
Craig LambieAuthor Commented:
The BulkLoad program uses a simple set of ComplexType = a Table and fields under it = Fields in that table.

The mapping of the nodes in an XML to Tables and Fields is the thing I am struggling with to be honest, as in what is the easiest way to do it, dynamically.
If I could easily iterate through the XSD for all complex type fields, then maybe I could get a list of all the possible outcomes and then just simple map their Xpaths to fields in a table and lookup the Xpath to know what table it went into.

So for example, I have a table called ToPole, with fields called PickID, Location, LocationName etc.  I used XMLSpy to create the DB Structure, then added the PK and FKs to it basically to create the Structure in pretty much the same schema as the XSD.
0
Anthony PerkinsCommented:
I don't think I explained myself very well.  All I need is for you to tell me what node maps to what column in what table.  For example, I suspect the "Voltage" node maps to the Voltage column in the Mains table. So can you confirm this and let me know all the other nodes you need mapped.
0
Anthony PerkinsCommented:
Incidentally, in addition to the two methods covered here, you can also import the data using SSIS.  But unfortunately I have no experience with that.
0
Craig LambieAuthor Commented:
The XSD file attached has all these mappings in it, that is the idea with the Complex Types in the XSD, to mark it up with relationships in order to just import it using SQLXMLBulkLoad.

The Voltage node does indeed map to Mains.

I think I have only put a limited version of the XSD on here, all the nodes need to be mapped mind.
I had a look into SSIS and posted a question on it.  Not going to work.

Thoughts on the one to Many issue with Voltage would be great, thanks.
0
Anthony PerkinsCommented:
>>Thoughts on the one to Many issue with Voltage would be great, thanks.<<
To be honest I am not understanding the full problem, but to add the Voltage values to your Mains table and return the IDENTITY values added you would do something like this:

; WITH XMLNAMESPACES ('http://www.xxx.com.au/vms/v0.5' AS ns)
INSERT Mains(intToPoleID, Voltage)
OUTPUT INSERTED.intMainsID
SELECT  1,			-- This value would come from the ToPole table
		T.c.value('.', 'varchar(50)' ) Voltage
FROM    @doc.nodes ('ns:InspectionWorkStatus/ns:Spans/ns:Span/ns:ToPole/ns:Mains/ns:Voltage') T(c)

Open in new window

0
Craig LambieAuthor Commented:
Thanks, although not fully understanding, you gave me the answer.
0
Craig LambieAuthor Commented:
I realised this morning that SQLXMLBulkUpload was having issues with these 1-M relationships as to work in other situations it has to have a table in between

Span
PK intSpanID

Trees
PK intTreesID
FK intTreeID

Tree
PK intTreeID
FK intTreesID

Is really silly to have these tables just to keep the XML bulk uploaded happy, as this also causes some issues when downloading the XML out again using SQLXMLCommand.
So the answer is to use the OpenXML and write a stack of queries for each table, as per acperkins suggestion.

I think it is ridiculous that this hasn't been solved by Microsoft and the SQLXML team, but hey, I guess it is for simpler XML than this.
If anyone at MS wants to provide me with the source code for SQLXMLBulkUpload I will happily take a look and fix this issue.




0
Anthony PerkinsCommented:
>>I think it is ridiculous that this hasn't been solved by Microsoft and the SQLXML team, but hey, I guess it is for simpler XML than this.<<
No, it is because you are going about it in totally the wrong way:
When importing files (Xml, delimited or whatever) you should never ever import directly into Production tables. That is verboten in any shop I have worked and is usually an indication of inexperience in the subject.  Just remember the phrase garbage in garbage out.
Here is what you should be doing:
Import the files into staging tables.  These tables are indexed appropriately but do not have foreign key constraints.
Once you have validated all the data in its entirety, then and only then do you import them into your Production tables.

>>If anyone at MS wants to provide me with the source code for SQLXMLBulkUpload I will happily take a look and fix this issue.<<
Dream on.  If I thought this was really a problem I would be happy to bring it up wtih the Microsoft MVPs, but I prefer not to be laughed at. :)
0
Craig LambieAuthor Commented:
ac, the issue is that it should already be valid... that is what the XSD is for.  I don't see any reason for validating it again in a set of staging tables.  And I don't see how you can relate the records in staging tables easily either...?

The issues that are occuring in SQLXMLBulkLoad are not laughable.  You or anyone else I have seen has been able to solve them?  The only way to fix them was to provide another way to do it.
0
Anthony PerkinsCommented:
>>ac, the issue is that it should already be valid<<
The keyword is "should".  We are not

>>that is what the XSD is for. <<
All XSD can do is validate the format of the data.  That is not enough.

>>I don't see any reason for validating it again in a set of staging tables. <<
And that is your decision and your responsibility.  I can only tell you that it is a very bad idea.

>>And I don't see how you can relate the records in staging tables easily either...?<<
I think we have covered that already.  The easiest way to do that is by using the OUTPUT clause.

>>The issues that are occuring in SQLXMLBulkLoad are not laughable.<<
Let's put it this way:  This utility has been around for over 10 years.  It is not going to change.  So let's move on and either see if you can solve it using this tool or some other one.  Since you are so adamant that you do not need to validate anything, one big advantage of SQLXmlBulkLoad over SSIS, is that you do not need to use staging tables or drop your foreign key constraints in order to import directly into your Production tables.
0
Anthony PerkinsCommented:
P.S.  I think we have beat this thread to death, so I suggest we move on to the new realted thread you have started at:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26167630.html
0
Anthony PerkinsCommented:
Also it does not look like I finished my train of thought.
This:
The keyword is "should".  We are not

Should have read:
The keyword is "should".  We are not in the business of making assumptions.  If you are not certain it will not happen then trust me it will.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.