Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML schema - Element name used to identify item problem... Expand / Collapse
Author
Message
Posted Thursday, February 25, 2010 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 16, 2012 11:20 AM
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!!!
Post #872789
Posted Thursday, February 25, 2010 1:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 7,040, Visits: 12,967
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
Post #872983
Posted Thursday, February 25, 2010 2:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 16, 2012 11:20 AM
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>
Post #873008
Posted Thursday, February 25, 2010 3:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 7,040, Visits: 12,967
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
Post #873043
Posted Friday, February 26, 2010 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 16, 2012 11:20 AM
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).
Post #873410
Posted Friday, February 26, 2010 9:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 7,040, Visits: 12,967
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
Post #873562
Posted Monday, March 1, 2010 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 16, 2012 11:20 AM
Points: 7, Visits: 24
Lutz,

Just what the Dr ordered...!

Thanks,

Vicar
Post #874429
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse