July 6, 2011 at 6:00 pm
Hello,
I am building a stored procedure to import data from an XML file into a table in SQL Server. The XML file looks like this below. The XML file has attributes with mulitple values and elements with mulitple values.
An application inserts results into a XML file (c:\temp\results.xml), and I would like to try to import the results into a SQL Server table.
I have been looking at the sp_xml_preparedocument procedure and openxml method. Can these support opening an XML file? Seems like examples show the XML data as character strings.
Are there other ways to get data in a XML file into a SQl Server table?
Here is the XML data:
<LINNET>
<LOCATION_SHAPE status="MODIFIED" type="SPATIAL_ATTRIBUTE_RECALCULATION">
<LOCATION_SHAPE_ID>32000</LOCATION_SHAPE_ID>
<LOC_SHP_TYPE_ID>200011</LOC_SHP_TYPE_ID>
<SHAPE_SEQ_NUM>32000</SHAPE_SEQ_NUM>
<LOC_SHP_TYPE_FOLDER_ID>100012</LOC_SHP_TYPE_FOLDER_ID>
<SPATIAL_ATTRIBUTES deleteOldValues="YES">
<ATTRIBUTE_DEFINITION>
<ATTRIBUTE name="AREA" type="ACREAGE" />
<ATTRIBUTE name="INVENTORY" type="CUSTOM" />
<ATTRIBUTE name="WILDLIFE_STREAM" type="CUSTOM" />
</ATTRIBUTE_DEFINITION>
<AREA value="38.9" unit_number="2000" />
<CUSTOM>
<NAME>INVENTORY</NAME>
<VALUE>25063</VALUE>
<AREA_OF_INTERSECTION value="36.2" unit_number="2000" />
</CUSTOM>
<CUSTOM>
<NAME>INVENTORY</NAME>
<VALUE>25377</VALUE>
<AREA_OF_INTERSECTION value="0.7" unit_number="2000" />
</CUSTOM>
</SPATIAL_ATTRIBUTES>
</LOCATION_SHAPE>
</LINNET>
July 6, 2011 at 6:25 pm
This will get the file into a temp table.
You have not said what you want to do with it from there....
IF OBJECT_ID('tempdb..#xmlTable') IS NULL
CREATE TABLE #xmlTable (xmlCol XML);
DECLARE @sql VARCHAR(MAX);
INSERT #xmlTable(xmlCol)
SELECT CONVERT(XML, BulkColumn) AS BulkColumnXml
FROM OPENROWSET (BULK 'c:\temp\results.xml', SINGLE_BLOB) AS a;
SELECT xmlCol
FROM #xmlTable;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 6, 2011 at 11:04 pm
Use magoo's code above to get the data into either a table or a variable. sp_prepare_document is only going to work with a single variable of data, not and enitre table worth unless you like loops. If you need to use an entire table, look at something like the NODES method or something similar. I believe FLOWR is another option. Been a while though.
Fraggle.
July 7, 2011 at 10:17 am
It appears the code will take the whole file and place it into a column in the table.
I am looking for is taking the data in the XML file and populate a table with the data in respected columns. I like to query on the data and build reports with SRRS. I would also need to loop thourgh each record in the XML, as there will be more than one.
I am thinking of using C#.Net which will give more flexibility.
July 7, 2011 at 4:54 pm
pbyrum (7/7/2011)
It appears the code will take the whole file and place it into a column in the table.I am looking for is taking the data in the XML file and populate a table with the data in respected columns. I like to query on the data and build reports with SRRS. I would also need to loop thourgh each record in the XML, as there will be more than one.
I am thinking of using C#.Net which will give more flexibility.
You are really not giving much information to work on here - perhaps if you could specify what it is you want and what you have tried it might go a bit quicker?
In the meantime, here is an example of how to shred the xml once you have it in the database, so you can populate your table(s)...
SELECT ls.value('(./LOCATION_SHAPE_ID)[1]','int') AS location_shape_id,
ls.value('(./LOC_SHP_TYPE_ID)[1]','int') AS loc_shp_type_id,
ls.value('(./SHAPE_SEQ_NUM)[1]','int') AS shape_seq_num,
ls.value('(./LOC_SHP_TYPE_FOLDER_ID)[1]','int') AS loc_shp_type_folder_id,
at.value('@name','varchar(100)') AS attribute_name
FROM #xmlTable
CROSS APPLY xmlCol.nodes('LINNET/LOCATION_SHAPE') AS Linnet(ls)
CROSS APPLY ls.nodes('./SPATIAL_ATTRIBUTES/ATTRIBUTE_DEFINITION/ATTRIBUTE') AS Attrib(at);
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 8, 2011 at 11:28 am
Thanks for T-SQL MM.
Basically, I like to import the data from the XML and import it into a table. Each data element would be placed in a column in the table. Like there would be a column name LOCATION_SHAPE_ID and there would be value in it. The hope is to process/import all data elments like this.
What I have tried is the bulk import of the XML file, so I was able to get the whole XML file and its data into one column in a table. This was a good start, but needed to parse out each data element and put them in their respected column.
Also, now my developer says I need to loop through multiple records in the XML.
I will work with the code that has provided and see what I can come up with.
Thanks for all of your great help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply