Anyone is working on PCATS XML standard?

[Bill Nguyen]

I am having problem opening the XAL file in the browser because the xmlns
URL and xsi URL are invalid.
If I would like to ignore those, what's the way to get the data into an
SQLserver table?

Thanks
Bill

<?xml version="1.0"?>
<pcats:NAXML-FuelsDoc
xmlns="http://www.naxml.org/Retail-EDI/Vocabulary/2003-10-16"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.naxml.org/Retail-EDI/Vocabulary/2003-10-16
NAXML-FuelPrice15.xsd" version="1.5">
<pcats:TransmissionHeader>
<pcats:TransmissionId>200705311139</pcats:TransmissionId>
<pcats:TransmissionDate>2007-05-31</pcats:TransmissionDate>
<pcats:TransmissionTime>11:39:17-06:00</pcats:TransmissionTime>
<pcats:TransmissionStatus>original</pcats:TransmissionStatus>
<pcats:TransmissionSender>Musket Corporation</pcats:TransmissionSender>
<pcats:TransmissionAgency>DTN Integrated
Services/dtnDataConnect</pcats:TransmissionAgency>
</pcats:TransmissionHeader>
<pcats:FuelPriceInfo>
<pcats:Terminal>
<pcats:Name identType="PlantNumber" ident="1132">
<pcats:NamePrefix>Stck ST Service</pcats:NamePrefix>
</pcats:Name>
<pcats:City>Stockton</pcats:City>
<pcats:State>CA</pcats:State>
<pcats:FuelProduct><pcats:EffectiveDate>2007-05-30</pcats:EffectiveDate>
<pcats:EffectiveTime>12:01:00</pcats:EffectiveTime>
<pcats:FuelProductId identType="Ultra Low Sulfur CARB
#2">0402</pcats:FuelProductId>
<pcats:Price>2.210000</pcats:Price>
<pcats:NetChange>.000000</pcats:NetChange>
</pcats:FuelProduct>
</pcats:Terminal>
</pcats:FuelPriceInfo>
</pcats:NAXML-FuelsDoc>

[John Saunders [MVP]]

"Bill Nguyen" <bi*****************@jaco.comwrote in message
news:el**************@TK2MSFTNGP04.phx.gbl...

>I am having problem opening the XAL file in the browser because the xmlns
URL and xsi URL are invalid.
If I would like to ignore those, what's the way to get the data into an
SQLserver table?

The xmlns are not web locations. The problem would be that
"NAXML-FuelPrice15.xsd" is not being found. Try removing the entire
xsi:schemaLocation attribute.
--
John Saunders [MVP]

[Bill Wade]

Contact PCATS at http://www.pcats.org to acquire a copy of the schema.
Then group the schema files in the directory with your XML file to allow
the browser to open and validate the XML.

If you want to process the file in code without access to the XSD files,
use an XMLReader with an XMLReaderSettings object that has the
XMLReaderSettings.ValidationType set to ValidationType.None.

For example, the code below employs an XSL file to transform the XML
into the desired format (output to a file thru a StreamWriter). Given
that you're processing a bill of lading document, you may want to load
the XML into an XMLDocument object and peel out the pieces you need.

using (FileStream fs = File.Open(uploadFilename, FileMode.Create,
FileAccess.ReadWrite, FileShare.None))
{
FileInfo SourceFile = new FileInfo(XmlFilePath);
// Establish the settings for the XmlReader
XmlReaderSettings ReaderSettings = new XmlReaderSettings();
ReaderSettings.CloseInput = true;
ReaderSettings.ConformanceLevel = ConformanceLevel.Document;
ReaderSettings.ProhibitDtd = false;
// Disable validation
ReaderSettings.ValidationType = ValidationType.None;
// Create the reader
XmlReader Reader = XmlReader.Create(SourceFile.FullName,
ReaderSettings);
XPathDocument XPathDoc = new XPathDocument(Reader);
XPathNavigator XPathNav = XPathDoc.CreateNavigator();
// Create some XsltSettings and load the stylesheet
XsltSettings XSLTSettings = new XsltSettings(false, true);
XslCompiledTransform XSLT = new XslCompiledTransform();
XSLT.Load(StyleSheet);
// Do the XSLT transform.
XSLT.Transform(XPathDoc, XSLTArgs, fs);
Reader.Close();
fs.Close();
}
*** Sent via Developersdex http://www.developersdex.com ***

[Bill Nguyen]

Thanks a lot Bill & John!

Bill

"Bill Wade" <bw***@profdata.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

Contact PCATS at http://www.pcats.org to acquire a copy of the schema.
Then group the schema files in the directory with your XML file to allow
the browser to open and validate the XML.

If you want to process the file in code without access to the XSD files,
use an XMLReader with an XMLReaderSettings object that has the
XMLReaderSettings.ValidationType set to ValidationType.None.

For example, the code below employs an XSL file to transform the XML
into the desired format (output to a file thru a StreamWriter). Given
that you're processing a bill of lading document, you may want to load
the XML into an XMLDocument object and peel out the pieces you need.

using (FileStream fs = File.Open(uploadFilename, FileMode.Create,
FileAccess.ReadWrite, FileShare.None))
{
FileInfo SourceFile = new FileInfo(XmlFilePath);
// Establish the settings for the XmlReader
XmlReaderSettings ReaderSettings = new XmlReaderSettings();
ReaderSettings.CloseInput = true;
ReaderSettings.ConformanceLevel = ConformanceLevel.Document;
ReaderSettings.ProhibitDtd = false;
// Disable validation
ReaderSettings.ValidationType = ValidationType.None;
// Create the reader
XmlReader Reader = XmlReader.Create(SourceFile.FullName,
ReaderSettings);
XPathDocument XPathDoc = new XPathDocument(Reader);
XPathNavigator XPathNav = XPathDoc.CreateNavigator();
// Create some XsltSettings and load the stylesheet
XsltSettings XSLTSettings = new XsltSettings(false, true);
XslCompiledTransform XSLT = new XslCompiledTransform();
XSLT.Load(StyleSheet);
// Do the XSLT transform.
XSLT.Transform(XPathDoc, XSLTArgs, fs);
Reader.Close();
fs.Close();
}
*** Sent via Developersdex http://www.developersdex.com ***

[Bill Nguyen]

Bill;
I can seem to obtain the schema from Pcats.org
I am completely new to XML in .NET environment.

Can I just create a DTD based on the XML file and use it with my app (VB.NET
2005)? Otherwise, can you give me some idea on how to setup XSL for the
following data elements?
Thanks a million!

Bill
-------------

<?xml version="1.0"?>
<pcats:NAXML-FuelsDoc
xmlns="http://www.naxml.org/Retail-EDI/Vocabulary/2003-10-16"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.naxml.org/Retail-EDI/Vocabulary/2003-10-16
NAXML-FuelPrice15.xsd" version="1.5">
<pcats:TransmissionHeader>
<pcats:TransmissionId>200607311642</pcats:TransmissionId>
<pcats:TransmissionDate>2006-07-31</pcats:TransmissionDate>
<pcats:TransmissionTime>16:42:23-06:00</pcats:TransmissionTime>
<pcats:TransmissionStatus>original</pcats:TransmissionStatus>
<pcats:TransmissionSender>BP Products Unbranded</pcats:TransmissionSender>
<pcats:TransmissionAgency>DTN Integrated
Services/dtnDataConnect</pcats:TransmissionAgency>
</pcats:TransmissionHeader>
<pcats:FuelPriceInfo>
<pcats:Terminal>
<pcats:Name identType="PlantNumber" ident="1074">
<pcats:NamePrefix>LngB BP Oil</pcats:NamePrefix>
</pcats:Name>
<pcats:City>Long Beach</pcats:City>
<pcats:State>CA</pcats:State>
<pcats:FuelProduct><pcats:EffectiveDate>2006-07-31</pcats:EffectiveDate>
<pcats:EffectiveTime>12:30:00</pcats:EffectiveTime>
<pcats:FuelProductId identType="Ultra Low Sulfur CARB
#2">0402</pcats:FuelProductId>
<pcats:Price>2.185000</pcats:Price>
<pcats:NetChange>-.007500</pcats:NetChange>
</pcats:FuelProduct>
</pcats:Terminal>
</pcats:FuelPriceInfo>
</pcats:NAXML-FuelsDoc>

"Bill Wade" <bw***@profdata.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

Contact PCATS at http://www.pcats.org to acquire a copy of the schema.
Then group the schema files in the directory with your XML file to allow
the browser to open and validate the XML.

If you want to process the file in code without access to the XSD files,
use an XMLReader with an XMLReaderSettings object that has the
XMLReaderSettings.ValidationType set to ValidationType.None.

For example, the code below employs an XSL file to transform the XML
into the desired format (output to a file thru a StreamWriter). Given
that you're processing a bill of lading document, you may want to load
the XML into an XMLDocument object and peel out the pieces you need.

using (FileStream fs = File.Open(uploadFilename, FileMode.Create,
FileAccess.ReadWrite, FileShare.None))
{
FileInfo SourceFile = new FileInfo(XmlFilePath);
// Establish the settings for the XmlReader
XmlReaderSettings ReaderSettings = new XmlReaderSettings();
ReaderSettings.CloseInput = true;
ReaderSettings.ConformanceLevel = ConformanceLevel.Document;
ReaderSettings.ProhibitDtd = false;
// Disable validation
ReaderSettings.ValidationType = ValidationType.None;
// Create the reader
XmlReader Reader = XmlReader.Create(SourceFile.FullName,
ReaderSettings);
XPathDocument XPathDoc = new XPathDocument(Reader);
XPathNavigator XPathNav = XPathDoc.CreateNavigator();
// Create some XsltSettings and load the stylesheet
XsltSettings XSLTSettings = new XsltSettings(false, true);
XslCompiledTransform XSLT = new XslCompiledTransform();
XSLT.Load(StyleSheet);
// Do the XSLT transform.
XSLT.Transform(XPathDoc, XSLTArgs, fs);
Reader.Close();
fs.Close();
}
*** Sent via Developersdex http://www.developersdex.com ***

[Bill Wade]

I am having problem opening the XAL file in the browser because the xmlns
URL and xsi URL are invalid.
If I would like to ignore those, what's the way to get the data into an
SQLserver table?

Thanks
Bill

<?xml version="1.0"?>
<pcats:NAXML-FuelsDoc
xmlns="http://www.naxml.org/Retail-EDI/Vocabulary/2003-10-16"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.naxml.org/Retail-EDI/Vocabulary/2003-10-16
NAXML-FuelPrice15.xsd" version="1.5">
<pcats:TransmissionHeader>
<pcats:TransmissionId>200705311139</pcats:TransmissionId>
<pcats:TransmissionDate>2007-05-31</pcats:TransmissionDate>
<pcats:TransmissionTime>11:39:17-06:00</pcats:TransmissionTime>
<pcats:TransmissionStatus>original</pcats:TransmissionStatus>
<pcats:TransmissionSender>Musket Corporation</pcats:TransmissionSender>
<pcats:TransmissionAgency>DTN Integrated
Services/dtnDataConnect</pcats:TransmissionAgency>
</pcats:TransmissionHeader>
<pcats:FuelPriceInfo>
<pcats:Terminal>
<pcats:Name identType="PlantNumber" ident="1132">
<pcats:NamePrefix>Stck ST Service</pcats:NamePrefix>
</pcats:Name>
<pcats:City>Stockton</pcats:City>
<pcats:State>CA</pcats:State>
<pcats:FuelProduct><pcats:EffectiveDate>2007-05-30</pcats:EffectiveDate>
<pcats:EffectiveTime>12:01:00</pcats:EffectiveTime>
<pcats:FuelProductId identType="Ultra Low Sulfur CARB
#2">0402</pcats:FuelProductId>
<pcats:Price>2.210000</pcats:Price>
<pcats:NetChange>.000000</pcats:NetChange>
</pcats:FuelProduct>
</pcats:Terminal>
</pcats:FuelPriceInfo>
</pcats:NAXML-FuelsDoc>

Email jh*****@pcats.org to get a copy of the schema files...

That aside, how did you want to load the data into your SQL table? An XSL is very useful for transforming the format from XML to CSV, but you may want to get the data into a .NET DataSet instead in order to save it to SQL. Another method would be to convert the data to a SQL bulk insert format, for which an XSL would be useful. What version of SQL Server are you using, and how many transactions do you expect to process?

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities