SQL import: unknown xml tags

Question

Here's my issue, I have an xml document that i need to import into my sql 2005 database daily.  However the xml document normally has 5 columns that will be tagged normally, but on any given day there can be up to 1,2,3 or more columns without any notification, and then the next day they might not be there again. 

How do i write the t-sql code to deal with these unknown columns?

Any assistance would be greatly welcomed.

Terry

Answer 1

Can you give an example of what the document looks like with and without those columns?

How are you doing the import?

---

--Brad (My Blog)

Answer 2

You do not have to know the element names to parse the XML. Here is example:

CREATE TABLE Foo (
 keycol INT PRIMARY KEY,
 xmlcol XML);
 
INSERT INTO Foo VALUES(1,
N'<root><amount>$2000</amount><foo>100-300</foo><name>Joe</name></root>');

SELECT keycol, 
       X.col.value('local-name(.)', 'VARCHAR(20)') AS key_value,
       X.col.value('.', 'VARCHAR(30)') AS data_value
FROM Foo AS F
CROSS APPLY xmlcol.nodes('/root/*') AS X(col);

/*

keycol      key_value            data_value
----------- -------------------- ------------------------------
1           amount               $2000
1           foo                  100-300
1           name                 Joe

*/

DROP TABLE Foo;

---

Plamen Ratchev

Answer 3

Here is a small section of one of the xml document i need to import.  As you will notice there are invoice numbers like 2-S7'S, S14, S55, S105.  These will always be in the document, however every so ofter there might be a new entry, these entries are usually tagged with "R" to start them, something like R268, R502, or R806.  Like I stated in my question, these might be there one day and gone the next.  There can be any number of them.  The code that i need is to catch any of them and create the new fields(if nessacary) and import them in the database automatically.

TO answer your question, we are currently using a vb.net application that runs once a day to run the sql statements.

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="http://abc.com/vss/Summary.xsl"?>
<SUMMARY><NAME>ABC</NAME><ID>1-3HIGU</ID><TIMESTAMP>11/04/2009</TIMESTAMP><SCHEDULED_DATE>11/05/2009</SCHEDULED_DATE><STATEMENT_ID>ABC_11052009</STATEMENT_ID><LINE_ITEMS><CHG_TYP><CHG_TYP_ID>DA</CHG_TYP_ID><CHG_TYP_NM>DA_A</CHG_TYP_NM><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S7</STLMT_TYP_CD><OPERATING_DATE>10/22/2009</OPERATING_DATE><AMT>1612.85</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="N">S7</STLMT_TYP_CD><OPERATING_DATE>10/29/2009</OPERATING_DATE><AMT>1598.27</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S14</STLMT_TYP_CD><OPERATING_DATE>10/22/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S55</STLMT_TYP_CD><OPERATING_DATE>09/11/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S105</STLMT_TYP_CD><OPERATING_DATE>07/23/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><INV_TOTAL><AMT>1612.85</AMT></INV_TOTAL></CHG_TYP><CHG_TYP><CHG_TYP_ID>DA_ASM_REG</CHG_TYP_ID><CHG_TYP_NM>DA_R_A</CHG_TYP_NM><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S7</STLMT_TYP_CD><OPERATING_DATE>10/22/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="N">S7</STLMT_TYP_CD><OPERATING_DATE>10/29/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S14</STLMT_TYP_CD><OPERATING_DATE>10/22/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S55</STLMT_TYP_CD><OPERATING_DATE>09/11/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S105</STLMT_TYP_CD><OPERATING_DATE>07/23/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><INV_TOTAL><AMT>0.00</AMT></INV_TOTAL></CHG_TYP></LINE_ITEMS></SUMMARY>

any assistance would be welcomed.

Terry

Answer 4

The S7 and S14 and S55 and S105 look like values and not columns.  The sporadic "R" entries are just other values.  In other words, all of these just look like STLMT_TYP_CD values.  So wouldn't they just go into a STLMT_TYP_CD column in a staging table of some kind?

If the "R" entries are truly new elements or tags within the XML, then I think Plamen gave you an idea of how to find those.

---

--Brad (My Blog)

Answer 5

I kinda feel real stupid right about know, I was thinking about the xml document last night an realized that they were values an not new tags myself.  I don't know what i was thinking about.

Sorry.