XML DOC into Table
-
I have the following XML Doc (Showing first record only): I need to know how I can break this down into approriate tables to insert the whole file into.
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints! -
Look for sp_xml_preparedocument and OPENXML in Books On Line. There are some samples. In general, once you have document handle, you can use XML data as you were using data table.
HTH
Piotr
...and your only reply is slàinte mhath
-
Thanks for the tip, but the end result will be the data within the XML document will be stored into SQL tables. What I need is to take the schema from the XML document and create the tables that the XML data will be inserted into. I am having trouble reading the XML file to derive the columns I need in the table.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints! -
Alorenzini ,
This site does not take to XML docs very well, trying posting your XML doc with brackets instead.
The following example shows how to insert via using the XQuery.Nodes method. This is an alternative to using OPENXML. Dont forget to change the brackets to greater than/less than signs.
DECLARE @xml XML
SET @xml = '
[Customer]
[CustomerDetails CustomerID="1" CompanyName="Company1"/]
[CustomerDetails CustomerID="2" CompanyName="Company2"/]
[CustomerDetails CustomerID="3" CompanyName="Company3"/]
[CustomerDetails CustomerID="4" CompanyName="Company4"/]
[CustomerDetails CustomerID="5" CompanyName="Company5"/]
[CustomerDetails CustomerID="6" CompanyName="Company6"/]
[CustomerDetails CustomerID="7" CompanyName="Company7"/]
[CustomerDetails CustomerID="8" CompanyName="Company8"/]
[/Customer]'
Declare @Customer table
(
CustomerID int,
CompanyName varchar(50)
)
INSERT INTO @Customer (CustomerID,CompanyName)
SELECT
a.col.value('@CustomerID', 'INT'),
a.col.value('@CompanyName', 'varchar(50)')
FROM
@xml.nodes('/Customer/CustomerDetails') AS a(col) --need to have a table and a column name
--Where a.col.value('@CustomerID', 'INT') = 7 --you can set filter here
select * from @Customer
-
See if this helps:
- [ArrayOfExportBusinessEntity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"]
- [ExportBusinessEntity]
[ExternalReference xmlns="http://www.thatchertech.com/Prowess/Services"]247378[/ExternalReference]
[ChangeType xmlns="http://www.thatchertech.com/Prowess/Services"]5[/ChangeType]
- [Salutation xmlns="http://www.thatchertech.com/Prowess/Services" CodeID="MS"]
[CodeGuid IsNull="false" DecimalValue="4394317858950256120563031138" /]
[/Salutation]
[FirstName xmlns="http://www.thatchertech.com/Prowess/Services"]April[/FirstName]
[PreferredName xmlns="http://www.thatchertech.com/Prowess/Services"]April[/PreferredName]
[LastName xmlns="http://www.thatchertech.com/Prowess/Services"]Juarez[/LastName]
[AuditNumber xmlns="http://www.thatchertech.com/Prowess/Services" IsNull="false" DecimalValue="90701" /]
- [BusinessEntityType xmlns="http://www.thatchertech.com/Prowess/Services" CodeID="OTH"]
[CodeGuid IsNull="false" DecimalValue="74661870252199532868249601760" /]
[/BusinessEntityType]
- [Culture xmlns="http://www.thatchertech.com/Prowess/Services" CultureID="en-US"]
[CultureGuid IsNull="false" DecimalValue="66271821171289482454621604260" /]
[/Culture]
- [Currency xmlns="http://www.thatchertech.com/Prowess/Services" CurrencyID="USD"]
[CurrencyGuid IsNull="false" DecimalValue="70604611308788313584415728663" /]
[/Currency]
[BusinessEntityEntryStatus xmlns="http://www.thatchertech.com/Prowess/Services" /]
- [BusinessEntityAddresses xmlns="http://www.thatchertech.com/Prowess/Services"]
- [BusinessEntityAddress]
[AdditionalFields /]
[AddressLine1]9246 FOLIAGE LANE[/AddressLine1]
[City]Munster[/City]
[PostalCode]46321[/PostalCode]
- [State StateCode="IN"]
[StateGuid IsNull="false" DecimalValue="23567725519222135717227012983" /]
[/State]
- [Country CountryCode="United States"]
[CountryGuid IsNull="false" DecimalValue="63176971073076024173865642249" /]
[/Country]
[NlsCounty]LAKE[/NlsCounty]
- [IsWithinCityLimits FieldName="IsWithinCityLimits"]
[FieldValue]true[/FieldValue]
[/IsWithinCityLimits]
[/BusinessEntityAddress]
[/BusinessEntityAddresses]
- [BusinessEntityCommunication xmlns="http://www.thatchertech.com/Prowess/Services"]
[AdditionalFields /]
[EmailAddress]Ajuarez901@aol.com[/EmailAddress]
[/BusinessEntityCommunication]
- [BusinessEntityPersonalDate xmlns="http://www.thatchertech.com/Prowess/Services"]
[AdditionalFields /]
[/BusinessEntityPersonalDate]
- [BusinessEntityPersonalInformation xmlns="http://www.thatchertech.com/Prowess/Services"]
[AdditionalFields /]
[/BusinessEntityPersonalInformation]
- [BusinessEntityPhone xmlns="http://www.thatchertech.com/Prowess/Services"]
[AdditionalFields /]
[HomePhone]2198385644[/HomePhone]
[/BusinessEntityPhone]
- [Businesses xmlns="http://www.thatchertech.com/Prowess/Services"]
- [Business]
- [BusinessIdentifier]
[BusinessGuid IsNull="false" DecimalValue="56591069079999257054502163486" /]
[BusinessEntityNumber IsNull="false" DecimalValue="268" /]
- [GenealogyPlan PlanID="TSIUSA"]
[PlanGuid IsNull="false" DecimalValue="4464265550645967577005996723" /]
[/GenealogyPlan]
[EZeroFilledBeeNumber]000000268[/EZeroFilledBeeNumber]
[/BusinessIdentifier]
- [GenealogyCompLevel LevelID="CONS"]
[CompLevelGuid IsNull="false" DecimalValue="63283361267641893250454546428" /]
[/GenealogyCompLevel]
- [GenealogyStatus StatusID="INACTIVE"]
[StatusGuid IsNull="false" DecimalValue="8456199997921863509428229548" /]
[/GenealogyStatus]
- [BusinessEntityBusinessDate]
- [AdditionalFields]
- [DateTimeField FieldName="ActivatedDate"]
[FieldValue IsNull="false" DateTimeValue="1999-03-25T00:00:00-05:00" /]
[/DateTimeField]
- [DateTimeField FieldName="ConsultantPromDt"]
[FieldValue IsNull="false" DateTimeValue="1999-03-25T00:00:00-05:00" /]
[/DateTimeField]
- [DateTimeField FieldName="ContractReceive"]
[FieldValue IsNull="false" DateTimeValue="2000-06-23T09:56:42-05:00" /]
[/DateTimeField]
- [DateTimeField FieldName="DeactivateDt"]
[FieldValue IsNull="false" DateTimeValue="2002-01-01T00:00:00-06:00" /]
[/DateTimeField]
- [DateTimeField FieldName="FIFTHANNIVERSARYDATE"]
[FieldValue IsNull="false" DateTimeValue="2004-03-25T00:00:00-05:00" /]
[/DateTimeField]
- [DateTimeField FieldName="InactiveDate"]
[FieldValue IsNull="false" DateTimeValue="2002-01-01T00:00:00-06:00" /]
[/DateTimeField]
- [DateTimeField FieldName="NACDate"]
[FieldValue IsNull="false" DateTimeValue="1999-03-16T00:00:00-05:00" /]
[/DateTimeField]
- [DateTimeField FieldName="TENTHANNIVERSARYDATE"]
[FieldValue IsNull="false" DateTimeValue="2009-03-25T00:00:00-05:00" /]
[/DateTimeField]
[/AdditionalFields]
[InitialAgreementDate IsNull="false" DateTimeValue="1999-03-16T00:00:00-05:00" /]
[/BusinessEntityBusinessDate]
- [BusinessEntityBusinessInformation]
- [AdditionalFields]
- [BooleanField FieldName="AcceptTerms"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="AllowShop"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="ApprShop"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="BlastKitOrdered"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="CityLimits"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="ClientDirectMrktgOptOut"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="DirectDepositInformation"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="DSAMEMBERNO"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="DSAMEMBERYES"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="GourmetGazetteOpt"]
[FieldValue]true[/FieldValue]
[/BooleanField]
- [StringField FieldName="HoldStatus"]
[FieldValue]N[/FieldValue]
[/StringField]
- [BooleanField FieldName="InvalidAddress"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="InvalidEmailAddress"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="IsBankInfoEntered"]
[FieldValue]false[/FieldValue]
[/BooleanField]
+ [BooleanField FieldName="LdrshpTrngCompliance"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="LeadershipLinkOpt"]
[FieldValue]true[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="ManualOrderForm"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="NewsLetterCompliance"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="OnLeadGrid"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="OpenMtgCompliance"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="PartyToSuccess"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="PrelimDeact"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="PressReleaseAllowed"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="RecognitionAwardOptOut"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="RecognitionOptOut"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="RegionalNewsletterOpt"]
[FieldValue]true[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="RestrictCompLevel"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [StringField FieldName="ShipToPhone"]
[FieldValue]219/838-5644[/FieldValue]
[/StringField]
- [BooleanField FieldName="SrTeamMentorBound"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="TeamMentorBound"]
[FieldValue]false[/FieldValue]
[/BooleanField]
- [BooleanField FieldName="VirtualCheckInformation"]
[FieldValue]false[/FieldValue]
[/BooleanField]
[/AdditionalFields]
[/BusinessEntityBusinessInformation]
- [BusinessEntityUplineLevels]
- [BusinessEntityUplineLevel]
- [GenealogyUplineLevel LevelName="Original Sponsor"]
[UplineLevelGuid IsNull="false" DecimalValue="50795058381470889633348545837" /]
[/GenealogyUplineLevel]
- [BusinessEntityBusinessUpline]
- [BusinessIdentifier]
[BusinessGuid IsNull="false" DecimalValue="870469128141339632278809531" /]
[BusinessEntityNumber IsNull="false" DecimalValue="96" /]
- [GenealogyPlan PlanID="TSIUSA"]
[PlanGuid IsNull="false" DecimalValue="4464265550645967577005996723" /]
[/GenealogyPlan]
[EZeroFilledBeeNumber]000000096[/EZeroFilledBeeNumber]
[/BusinessIdentifier]
[/BusinessEntityBusinessUpline]
[/BusinessEntityUplineLevel]
- [BusinessEntityUplineLevel]
- [GenealogyUplineLevel LevelName="1st Line"]
[UplineLevelGuid IsNull="false" DecimalValue="49247633332364056541834332694" /]
[/GenealogyUplineLevel]
- [BusinessEntityBusinessUpline]
- [BusinessIdentifier]
[BusinessGuid IsNull="false" DecimalValue="73546253700094502672254523130" /]
[BusinessEntityNumber IsNull="false" DecimalValue="999999996" /]
- [GenealogyPlan PlanID="TSIUSA"]
[PlanGuid IsNull="false" DecimalValue="4464265550645967577005996723" /]
[/GenealogyPlan]
[EZeroFilledBeeNumber]999999996[/EZeroFilledBeeNumber]
[/BusinessIdentifier]
[/BusinessEntityBusinessUpline]
[/BusinessEntityUplineLevel]
[/BusinessEntityUplineLevels]
[/Business]
[/Businesses]
- [Contracts xmlns="http://www.thatchertech.com/Prowess/Services"]
- [BusinessEntityContractInfo]
[UserName]WebIceEntry[/UserName]
[ContractSource]WEBSERVICE[/ContractSource]
[/BusinessEntityContractInfo]
[/Contracts]
[LastModifiedTime xmlns="http://www.thatchertech.com/Prowess/Services" IsNull="false" DateTimeValue="2007-12-07T16:07:57.6-06:00" /]
[/ExportBusinessEntity]
The XML has several namespaces and I am having problems with how to translate it into SQL Tables.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints! -
Would each namespace equate itself to a table?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints! -
Here are a couple of queries against your XML. The tables you load the XML into will typically be dependent upon the 1:n relationships in the data.
declare @x xmlset @x='
... [your XML here]
';
with xmlnamespaces('http://www.thatchertech.com/Prowess/Services' as n1)
select r.value('n1:ExternalReference[1]','int') as ExternalReference,
r.value('n1:ChangeType[1]','int') as ChangeType,
r.value('n1:Salutation[1]/@CodeID','varchar(30)') as Salutation,
r.value('(n1:Salutation/n1:CodeGuid)[1]/@DecimalValue','varchar(30)') as DecimalValue
from @x.nodes('/ArrayOfExportBusinessEntity/ExportBusinessEntity') as x(r)
;
with xmlnamespaces('http://www.thatchertech.com/Prowess/Services' as n1)
select r.value('(../../../n1:BusinessIdentifier/n1:BusinessGuid)[1]/@DecimalValue','varchar(30)') as DecimalValue,
r.value('@FieldName','varchar(30)') as FieldName,
r.value('n1:FieldValue[1]/@DateTimeValue','varchar(30)') as DateTimeValue
from @x.nodes('/ArrayOfExportBusinessEntity/ExportBusinessEntity/n1:Businesses/n1:Business/n1:BusinessEntityBusinessDate/n1:AdditionalFields/n1:DateTimeField') as x(r)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi,
This might give answer for your question.
DROP TABLE TEST
CREATE TABLE TEST
(x xml)
INSERT TEST VALUES(
N'
')
SELECT ref.value('@name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM TEST CROSS APPLY x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
thanks 🙂
-
Can you explain what is going on here?
select r.value('(../../../n1:BusinessIdentifier/n1:BusinessGuid)[1]/@DecimalValue','varchar(30)') as DecimalValue,...
It returning a empty recordset.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints! -
How can I get this section to parse out:
[BusinessEntityAddresses xmlns="http://www.thatchertech.com/Prowess/Services"]
[BusinessEntityAddress]
[AdditionalFields /]
[AddressLine1]9246 FOLIAGE LANE[/AddressLine1]
[City]Munster[/City]
[PostalCode]46321[/PostalCode]
[State StateCode="IN"]
[StateGuid IsNull="false" DecimalValue="23567725519222135717227012983" /]
[/State]
[Country CountryCode="United States"]
[CountryGuid IsNull="false" DecimalValue="63176971073076024173865642249" /]
[/Country]
[NlsCounty]LAKE
[IsWithinCityLimits FieldName="IsWithinCityLimits"]
true</FieldValue]
[/IsWithinCityLimits]
[/BusinessEntityAddress]
[/BusinessEntityAddresses]
I am trying to use the following code:
with xmlnamespaces('http://www.thatchertech.com/Prowess/Services' as n1)
select r.value('n1:ExternalReference[1]','int') as ExternalReference,
r.value('n1:ChangeType[1]','int') as ChangeType,
r.value('n1:Salutation[1]/@CodeID','varchar(30)') as Salutation,
--r.value('(n1:Salutation/n1:CodeGuid)[1]/@DecimalValue','varchar(30)') as SalutationCodeGuid,
r.value('n1:FirstName[1]','varchar(30)') as FirstName,
r.value('n1:PreferredName[1]','varchar(30)') as PreferredName,
r.value('n1:LastName[1]','varchar(30)') as LastName,
r.value('n1:AuditNumber[1]/@DecimalValue','varchar(30)') as AuditNumber,
r.value('n1:BusinessEntityType[1]/@CodeID','varchar(30)') as BusinessEntityType,
r.value('n1:Culture[1]/@CultureID','varchar(30)') as Culture,
r.value('n1:Currency[1]/@CurrencyID','varchar(30)') as Currency,
r.value('n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:AdditionalFields/AddressLine1[1]','varchar(30)') as AddressLine1
from @x.nodes('/ArrayOfExportBusinessEntity/ExportBusinessEntity') as x(r);
But I get the following error for this line:
Msg 2389, Level 16, State 1, Line 12591
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints! -
Try this
with xmlnamespaces('http://www.thatchertech.com/Prowess/Services' as n1)select r.value('n1:ExternalReference[1]','int') as ExternalReference,
r.value('n1:ChangeType[1]','int') as ChangeType,
r.value('n1:Salutation[1]/@CodeID','varchar(30)') as Salutation,
--r.value('(n1:Salutation/n1:CodeGuid)[1]/@DecimalValue','varchar(30)') as SalutationCodeGuid,
r.value('n1:FirstName[1]','varchar(30)') as FirstName,
r.value('n1referredName[1]','varchar(30)') as PreferredName,
r.value('n1:LastName[1]','varchar(30)') as LastName,
r.value('n1:AuditNumber[1]/@DecimalValue','varchar(30)') as AuditNumber,
r.value('n1:BusinessEntityType[1]/@CodeID','varchar(30)') as BusinessEntityType,
r.value('n1:Culture[1]/@CultureID','varchar(30)') as Culture,
r.value('n1:Currency[1]/@CurrencyID','varchar(30)') as Currency,
r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:AdditionalFields/n1:AddressLine1)[1]','varchar(30)') as AddressLine1
from @x.nodes('/ArrayOfExportBusinessEntity/ExportBusinessEntity') as x(r);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
I thought of that but I am still returning null values.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints! -
In your XML, AddressLine1 isn't a sub-element of AdditionalFields
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
I found that out. I took out the Additional Fields and it worked fine.
Thanks for all your help.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints! -
OK I don't quite have it:
r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:State/n1:StateCode)[1]','varchar(30)') as StateCode,
[State StateCode="IN"]
[StateGuid IsNull="false DecimalValue="23567725519222135717227012983" /]
[/State]
This is returning nulls.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply