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

Question

Post reply

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

Vicar

SSC Journeyman

Points: 79
More actions
February 25, 2010 at 8:24 am

#98996

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!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Vicar SSC Journeyman Points: 79 More actions · Answer 2

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">

<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>

<EMAIL>Smith@anywhere.com</EMAIL>

<CITY>Somewhere</CITY>

</CONTACT_PERSON>

</HEADER>

<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>

<NUMBER_OF_RECORDS>1</NUMBER_OF_RECORDS>

</TRAILER>

</HC_DATA>

LutzM SSC Guru Points: 107049 More actions · Answer 3

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Vicar SSC Journeyman Points: 79 More actions · Answer 4

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 SSC Guru Points: 107049 More actions · Answer 5

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Vicar SSC Journeyman Points: 79 More actions · Answer 6

Vicar

SSC Journeyman

Points: 79

March 1, 2010 at 8:07 am

#1126465

Lutz,

Just what the Dr ordered...!

Thanks,

Vicar