Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-26 : 09:29:09
|
| Andreas writes "Hi!I am having serious problems when trying to load an xml-file using XMLBulkLoad. The xml-file looks like this:<?xml version="1.0" encoding="ISO-8859-1" ?><RECEIPT_INDATA_V1> <SVAR> <SERIAL>1</SERIAL> <LOGGNR2>100</LOGGNR2> <ANT_AVVORS>01</ANT_AVVORS> <AVORS> <AVKOD></AVKOD> <REGELNR></REGELNR> <AVVTEXT>User not authorized</AVVTEXT> <AVVKOMPL></AVVKOMPL> </AVORS> <BEARBSTATUS>01</BEARBSTATUS> <STATUSTEXT>No orders ready</STATUSTEXT> <LEDTEXT></LEDTEXT> <TEXTBESK></TEXTBESK> </SVAR> <SVAR> <SERIAL>2</SERIAL> <LOGGNR2>101</LOGGNR2> <ANT_AVVORS>00</ANT_AVVORS> <BEARBSTATUS>00</BEARBSTATUS> <STATUSTEXT>No orders ready</STATUSTEXT> <LEDTEXT></LEDTEXT> <TEXTBESK></TEXTBESK> </SVAR></RECEIPT_INDATA_V1>I have managed to load the data in the tag <SVAR> into one table with the SERIAL as primary key. My problem is that I also would like to load the data in the tag <AVORS> to another table along with the data from the SERIAL tag. Thus, I want to be able to connect each AVORS tag with its parent SVAR tag. The shema I am using to loag the SVAR tag looks like this: <?xml version="1.0" encoding="ISO-8859-1" ?><Schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:xml:datatypes" xmlns:sql="urn:schemas-microsoft-com:xml-sql"><ElementType name="SERIAL" dt:type="int"/><ElementType name="LOGGNR2" dt:type="int"/><ElementType name="ANT_AVVORS" dt:type="string"/><ElementType name="BEARBSTATUS" dt:type="string"/><ElementType name="STATUSTEXT" dt:type="string"/><ElementType name="RECEIPT_INDATA_VI" sql:is-constant="1"> <element type="SVAR"/></ElementType><ElementType name="SVAR" sql:relation="MBRRawXMLData"> <element type="SERIAL" sql:field="SERIAL"/> <element type="LOGGNR2" sql:field="LOGGNR2"/> <element type="ANT_AVVORS" sql:field="ANT_AVVORS"/> <element type="BEARBSTATUS" sql:field="BEARBSTATUS"/> <element type="STATUSTEXT" sql:field="STATUSTEXT"/></ElementType></Schema>I need help modifying this schema to load the data in the AVORS tag + SERIAL from the SVAR tag into another table, e.g. MBRRaxXMLDataDenied. The thing is that the AVORS tag can occur many times in a SVAR tag. The number it occurs is the number found in the ANT_AVVORS tag.If you guys could help me with this you would really save my day.Thanks in advance./Andreas" |
|
|
|
|
|
SQLXMLBulkLoad