Help Loading XML

[pjw001]

Hi,

I do have some experience of loading XML into SQL server, but have come up against a problem and would appreciate some help. The XML I am trying to load is:

<WorkerManagement>
<Worker>
<WorkerID>1234</WorkerID>
<LegalName>
<Name Location="First" TypeCode="GivenName">GLORIA</Name>
<Name Location="Middle" TypeCode="GivenName">EDITH</Name>
<Name Location="Last" TypeCode="FamilyName">HERNANDEZ</Name>
<Name Location="Last" TypeCode="FamilyName">MARTINEZ</Name>
<Name Location="First" TypeCode="Initial">GH</Name>
</LegalName>
<WorkerTitle>aa</WorkerTitle>
<SecurityIdentifier TypeCode="BadgeNumber">00</SecurityIdentifier>
<SecurityIdentifier TypeCode="Password"> ************</SecurityIdentifier>
<PayRollID>640085</PayRollID>
<Classification FulltimeFlag="false" WorkerTypeID="1"></Classification>
<EffectiveDate>2001-01-01</EffectiveDate>
<ExpirationDate></ExpirationDate>
</Worker>
</WorkerManagement>

The lines that are giving me a problem are the ones starting "<Name Location...". I cannot get the actual Name (e.g.GLORIA, EDITH etc.) to load.

I am using SQL 2012 and using sp_xml_preparedocument to load the XML from a variable.

Any help would be much appreciated.

 

[gmmastros]

This part is weird.

<Name Location="Last" TypeCode="FamilyName">HERNANDEZ</Name>
<Name Location="Last" TypeCode="FamilyName">MARTINEZ</Name>

Should one of those TypeCodes be GivenName?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Try this:

Declare @XML VarChar(1000)
Set @XML = '
<WorkerManagement>
<Worker>
<WorkerID>1234</WorkerID>
<LegalName>
<Name Location="First" TypeCode="GivenName">GLORIA</Name>
<Name Location="Middle" TypeCode="GivenName">EDITH</Name>
<Name Location="Last" TypeCode="GivenName">HERNANDEZ</Name>
<Name Location="Last" TypeCode="FamilyName">MARTINEZ</Name>
<Name Location="First" TypeCode="Initial">GH</Name>
</LegalName>
<WorkerTitle>aa</WorkerTitle>
<SecurityIdentifier TypeCode="BadgeNumber">00</SecurityIdentifier>
<SecurityIdentifier TypeCode="Password"> ************</SecurityIdentifier>
<PayRollID>640085</PayRollID> 
<Classification FulltimeFlag="false" WorkerTypeID="1"></Classification> 
<EffectiveDate>2001-01-01</EffectiveDate>
<ExpirationDate></ExpirationDate>
</Worker>
</WorkerManagement>'

Declare @iDoc Int

exec sp_xml_Preparedocument @iDoc OUT, @XML

Select	*
From	OpenXML(@iDoc, 'WorkerManagement/Worker', 3)
With	(
        WorkerID Int,
        FirstName VarChar(50) 'LegalName/Name[@Location="First" and @TypeCode="GivenName"]',
        MiddleName VarChar(50) 'LegalName/Name[@Location="Middle"]',
        LastName VarChar(50) 'LegalName/Name[@Location="Last" and @TypeCode="GivenName"]',
        LastName_Family VarChar(50) 'LegalName/Name[@Location="Last" and @TypeCode="FamilyName"]',
        Initials varchar(50) 'LegalName/Name[@Location="First" and @TypeCode="Initial"]',
        WorkerTitle varchar(50),
        BadgeNumber varchar(100) 'SecurityIdentifier[@TypeCode="BadgeNumber"]',
        Password varchar(100) 'SecurityIdentifier[@TypeCode="Password"]',
        PayRollID Int,
        FullTime Bit 'Classification/@FulltimeFlag',
        WorkerTypeId int 'Classification/@WorkerTypeID'
        )

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pjw001]

That works.

Many thanks.