I have an xml data file. I have a t-sql stored procedure that uses openxml to do an insert based on this data. The procedure inserts data but not correctly. Only the data from the first level of the xml file gets inserted. Data nested under the first level all gets inserted into one field. My question is, how do I write the openxml so that data goes into the correct fields?
Next you will see the data file. You will see that the "2nd tier" of data starts at the <Page> tag. All the data under this tag is nested under <Page> and other levels; it's hard to see the way it is pasted here. When I do the insert, every piece of data under this tag all gets put into one field in the target table, the Page field. I want it all to go into its respective fields. Here is the xml data file.
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <JV xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance">
<JVNo>1</JVNo>
<Preparer>Dirk</Preparer>
<Date>03/08/2008</Date>
- <Page>
<PageNo>1</PageNo>
- <Entry No="1">
<AT>22</AT>
<Fund>110</Fund>
<Agency>100</Agency>
<Org>1100</Org>
<Object>2410</Object>
<Debit>50000</Debit>
<Credit>0</Credit>
<Annotation>This is a test</Annotation>
</Entry>
- <Entry No="2">
<AT>31</AT>
<Fund>320</Fund>
<Agency>110</Agency>
<Org>1101</Org>
<Object>3210</Object>
<Debit>0</Debit>
<Credit>50000</Credit>
<Annotation>This is a test</Annotation>
</Entry>
</Page>
<Comments>This is a test jv</Comments>
<GrandDebit>50000</GrandDe
bit>
<GrandCredit>50000</GrandC
redit>
</JV>
Following is the sql that does the insert.
INSERT INTO JV_TEST
SELECT *
FROM OPENXML (@rDoc,'/JV',3) WITH JV_Test
And here is the offending result; you can't tell but again, all of the data under the page tag got put into one field, the Page field.
NULL Dirk NULL 1 22 110 100 1100 2410 50000 0 This is a test 31 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 50000 50000
The target table has a field available for each piece of data. How do I write the openxml so that data gets put into the correct fields?
Start Free Trial
