SQL XML XSD Experts for Foreign Key pass down issue

Why mark it up with the XSD. Why not load the XML into a stored procedure and process there.

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

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

Select allOpen in new window

Ok, that looks great.

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

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

Select allOpen in new window

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?

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.

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?

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

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.

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

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.

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.

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.

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.

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

Select allOpen in new window

Thanks, although not fully understanding, you gave me the answer.

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.

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

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.

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

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:
https://www.experts-exchange.com/questions/26167630/Get-FK-from-past-insert-using-OpenXML-in-Sql-Server.html

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.