Prx_id = prxparse("/&delim_open&iElement(\s+.*?&delim_close|&delim_close)?(.*?)&delim_open\/&iElement&delim_close/i") Ĭall prxnext(prx_id, prx_start, prx_stop, &iXMLField, prx_pos, prx_length) Ĭall prxposn(prx_id, 3, prx_pos, prx_length) %put ERR%str()ROR (prxCount.sas): You specified an incorrect option for the iDelimiterType parameter. %let delim_close = %nrbquote(&)gt%quote(%str( )) %let delim_open = %nrbquote(&)lt%quote(%str( )) %else %if &iDelimiterType eq 3 %then %do %else %if &iDelimiterType eq 2 %then %do %macro prxCount(iElement=, iXMLField=, iDelimiterType=1) ** - MODIFIED TO ALLOW FOR OPTIONAL ATTRIBUTES ON THE ELEMENT BEING SEARCHED FOR. ** iXMLField : The name of the field that contains the XML blob to parse. ** iElement : The element to search through the blob for. ** RETURNS THE NUMBER OF TIMES A SEGMENT IS FOUND IN AN XML FILE.
#Datastruction shred company how to#
They shouldn't really need to understand how the macros work, they just need to understand how to call and use them. not stored over multiple observations in SAS). The only condition for the below macros, is that the XML is no longer than 32767 chars, and that it's all in a single character observation on a single row in SAS (ie.
#Datastruction shred company code#
If you provide these to the analysts, they should be able to run the code as-is, and working through the examples and parameters, extract some information for themselves to do preliminary investigations and maybe give you more concrete requirements. I don't have time right now to go through it in detail, but wanted to provide something that you may find useful in the meantime.
I've provided examples which I'm hoping will help explain how they work. We use the below two macros in SAS to extract certain attributes, elements, values, etc.
I figured I'd list these here as even if they don't help you, they may help someone else in future searching for a similar solution. "Import XML with an unknown structure" turned up a few suggested tools, but their output isn't that helpful. There's plenty of information out there (including several SO questions) about how to convert a known XML document to SQL, but I need to know how to import an arbitrary XML document. We've looked at using the xml Data Type Methods to get the data into a table like this: CREATE TABLE ResponseDataīut feel there must be a way to get more useful information separation than that. I've looked into using SQLXML Bulk Load to generate the tables, but this requires a SQL-annotated XSD schema, and there doesn't appear to be any tool to generate this. I've entertained the idea of writing C# code to generate a SQL schema based on the XML data, and hoping that, at least within the scope of an individual source, the structures could be made consistent enough to fit all the files. We've even looked into using Microsoft Excel, but it doesn't interpret the file correctly (it parses just fine, but it gives the columns nonsensical names). My experience is primarily in SQL Server, but if there are solutions that target other platforms, we are definitely open to that. It seems the answer would be "you want a document database", but apparently SAS needs either something flat, like a CSV or other wide-table structure, or something relational. There are no XSL, XSD, or XSLT documents available. While they all nominally describe the same thing - the (very detailed) results of a credit inquiry - they don't follow a consistent format, even with files that come from the same source, because the version of the document has changed significantly over time. The files have come from a half-dozen different sources over the course of six years. My job is to find a way to convert the XML files into a relatively flat data structure so they can be imported into SAS. The analytics tool they will be using is SAS, which I have never used - someone else will be doing the actual mining. My company has 20 GB of XML files that they want to do some data mining against.
Not actually a duplicate of Import arbitrary XML to SQL Server
0 kommentar(er)
