Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML schema - Element name used to identify item problem...


XML schema - Element name used to identify item problem...

Author
Message
Vicar
Vicar
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
I am working on a project for a State Agency where the data is received from multiple private entities and loaded into SQL 2005 tables. The XML schema was designed and published before my involvement with the project and now it cannot be changed. One of the elements to receive (Charges) occurs multiple times within a client but each is for a different concept. Instead of using an attribute to identify the reason for the Charge, they used the element name to do it. For example:

<FERTILIZER_CHARGE>2300</FERTILIZER_CHARGE> -- 20 different charge types identified

instead of

<CHARGE TYPE=”FERTILIZER” AMOUNT=”2300” />

I need to move all the charges to a single table and identify each one with its type but cannot find any examples where the element name is used to update a column in a table. PLEASE HELP!!!
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
Would you please provide some ready to use sample data as described in the first link in my signature?
Based on your (rather vague) sample data I'd simply use
c.value('FERTILIZER_CHARGE[1]', 'VARCHAR(30)')


but the subject obviously is a little more complex.
So, please help us help you.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Vicar
Vicar
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
Lutz,
My project is not realy for agriculture but its nature prohibits me from using anything that resembles its type so I have made many changes to the original values in my sample.

What I am trying to do is to read a single varbinary(max) that has the original XML text and move the data to a series of SQL 2005 tables. In the example below I need to move the header portion to one row in a table and then use the id assigned to that row by the identity column as foreign key to the others.

The Records portion will be written to a Clients table and the charges to the ClientCharges table. Of course the ID of the Header needs to be written to the Clients and the Client ID will need to be written to the ClientCharges as foreign keys.

The charges need to be written one per row and identified by the associated element tag. Something like ChargeAmount = 500 Type=Fertilizer (two columns in ClientCharges table)

Please keep in mind that the structure of the XML cannot be changed, it's goverment...

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<HC_DATA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://somefile.xsd">
<HEADER>
<TRANS_CODE>Q</TRANS_CODE>
<RPT_YEAR>2010</RPT_YEAR>
<RPT_QTR>1</RPT_QTR>
<DATA_TYPE>XXX-2</DATA_TYPE>
<SUBMISSION_TYPE>I</SUBMISSION_TYPE>
<PROCESS_DATE>2009-10-13</PROCESS_DATE>
<FACILITY_NUM>00100001</FACILITY_NUM>
<ORG_NAME>XML Data</ORG_NAME>
<CONTACT_PERSON>
<NAME>Mike Smith</NAME>
<PHONE>(888)222-1111</PHONE>
<EMAIL>Smith@anywhere.com</EMAIL>
<STREET>P.O. Box 222</STREET>
<CITY>Somewhere</CITY>
<STATE>CA</STATE>
<ZIP>52309</ZIP>
</CONTACT_PERSON>
</HEADER>
<RECORDS>
<RECORD id="458841">
<FACILITY_NUM>00100001</FACILITY_NUM>
<CLIENT_SSN>111223333</CLIENT_SSN>
<CLIENT_ETHNICITY>E7</CLIENT_ETHNICITY>
<CLIENT_RACE>4</CLIENT_RACE>
<CLIENT_BIRTHDATE>1997-12-24</CLIENT_BIRTHDATE>
<CLIENT_SEX>M</CLIENT_SEX>
<CLIENT_ZIP>40312</CLIENT_ZIP>
<CLIENT_COUNTRY>99</CLIENT_COUNTRY>
<TYPE_OF_SERVICE>1</TYPE_OF_SERVICE>
<PRINC_PAYER_CODE>B</PRINC_PAYER_CODE>
<FERTILIZER_CHARGES>500</FERTILIZER_CHARGES>
<HERBIZIDE_LEVEL_1_CHARGES>0</HERBIZIDE_LEVEL_1_CHARGES>
<HERBIZIDE_LEVEL_2_CHARGES>250</HERBIZIDE_LEVEL_2_CHARGES>
<HERBIZIDE_LEVEL_3_CHARGES>0</HERBIZIDE_LEVEL_3_CHARGES>
<SEED_CHARGES>0</SEED_CHARGES>
<MAINTENANCE_CHARGES>1000</MAINTENANCE_CHARGES>
</RECORD>
</RECORDS>
<TRAILER>
<NUMBER_OF_RECORDS>1</NUMBER_OF_RECORDS>
</TRAILER>
</HC_DATA>
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
Based on your sample data: what would be your expected result?



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Vicar
Vicar
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
One record in the Batches table (Header values), one record in the Clients table (Record values - this data happens to contain only one client) and six records in the Charges table (each of the individual charges).
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
That's my "wild-guessing-code" that might help you:

SELECT 
   c.value('TRANS_CODE[1]', 'VARCHAR(30)') AS Batches_col1
FROM @xml.nodes('//HEADER') t(c)

SELECT
   c.value('CLIENT_SSN[1]', 'VARCHAR(30)') AS Clients_col1
FROM @xml.nodes('//RECORD') t(c)

SELECT
   v.value ('@id[1]','VARCHAR(50)') AS Charges_col1,
   y.value('local-name(.)', 'VARCHAR(50)') AS Charges_col2,
   y.value('(.)', 'VARCHAR(50)') AS Charges_col3
FROM @xml.nodes('//RECORDS') t(c)
CROSS APPLY
t.c.nodes('RECORD') u(v)
CROSS APPLY
u.v.nodes('*') x(y)
WHERE y.value('local-name(.)', 'VARCHAR(50)') LIKE '%CHARGES'





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Vicar
Vicar
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
Lutz,

Just what the Dr ordered...!

Thanks,

Vicar
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search