Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing XML into SQL Server 2005

Status
Not open for further replies.
alextt

alextt

Programmer
Aug 15, 2006
1
GB
How to import following XML file into SQL Server 2005 table(s)? Thanks.


My XML file is
--===================================================
<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"
xmlns:xsi="xmlns:xsd=" <xsd:schema
targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
<xsd:element name="root">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="row" type="row" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:simpleType name="uuid">
<xsd:restriction base="xsd:string">
<xsd:pattern
value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="xmlDocument">
<xsd:sequence>
<xsd:any />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="row">
<xsd:sequence>
<xsd:element sql:field="METADATA" name="METADATA"
type="xmlDocument" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<row>
<xars:METADATA
xmlns="xmlns:xars="urn:schemas-microsoft-com:xml-analysis:rowset">
<Database>
<Name>Eurostop</Name>
<ID>Eurostop</ID>
<CreatedTimestamp>2006-07-13T10:21:29</CreatedTimestamp>
<LastSchemaUpdate>2006-07-26T09:40:16</LastSchemaUpdate>
<Description>NEW TEST VERSION on LTS Server</Description>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>PartiallyProcessed</State>
<LastUpdate>2006-07-26T09:40:16</LastUpdate>
<AggregationPrefix />
<Language>1033</Language>
<Collation>Latin1_General_CI_AS</Collation>
<Visible>true</Visible>
<MasterDataSourceID />
<ProcessingPriority>0</ProcessingPriority>
<EstimatedSize>869331</EstimatedSize>
<Translations />
<DataSourceImpersonationInfo>
<ImpersonationMode>Default</ImpersonationMode>
<Account />
<Password />
<ImpersonationInfoSecurity>Unchanged</ImpersonationInfoSecurity>
</DataSourceImpersonationInfo>
<Dimensions>
<Dimension>
<Name>Assistant</Name>
<ID>Assistant</ID>
<CreatedTimestamp>2006-07-13T10:21:29</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:29</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>AttendanceInputUsers</Name>
<ID>AttendanceInputUsers</ID>
<CreatedTimestamp>2006-07-13T10:21:29</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:29</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>BranchCountry</Name>
<ID>BranchCountry</ID>
<CreatedTimestamp>2006-07-13T10:21:29</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:29</LastSchemaUpdate>
<LastProcessed>2006-07-28T10:33:36</LastProcessed>
<State>Processed</State>
</Dimension>
<Dimension>
<Name>Classification</Name>
<ID>Classification</ID>
<CreatedTimestamp>2006-07-13T10:21:29</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:29</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>Colour</Name>
<ID>Colour</ID>
<CreatedTimestamp>2006-07-13T10:21:29</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:29</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>CountryBranchTill</Name>
<ID>CountryBranchTill</ID>
<CreatedTimestamp>2006-07-13T10:21:29</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:29</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>DiscountTypes</Name>
<ID>DiscountTypes</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>Hour</Name>
<ID>Hour</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>2006-08-03T14:07:19</LastProcessed>
<State>Processed</State>
</Dimension>
<Dimension>
<Name>Product</Name>
<ID>Product</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>SalesPerson</Name>
<ID>SalesPerson</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>Sizes</Name>
<ID>Sizes</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>Staff</Name>
<ID>Staff</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>Style</Name>
<ID>Style</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>Supplier</Name>
<ID>Supplier</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>Tender</Name>
<ID>Tender</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>TransactionType</Name>
<ID>TransactionType</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>Void</Name>
<ID>Void</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Dimension>
<Dimension>
<Name>YearMonthDay</Name>
<ID>YearMonthDay</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>2006-08-03T14:07:00</LastProcessed>
<State>Processed</State>
</Dimension>
<Dimension>
<Name>YearSeasonWeek</Name>
<ID>YearSeasonWeek</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>2006-08-03T14:06:23</LastProcessed>
<State>Processed</State>
</Dimension>
</Dimensions>
<Cubes>
<Cube>
<Name>Footfall</Name>
<ID>Footfall</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>2006-08-03T14:06:59</LastProcessed>
<State>Unprocessed</State>
</Cube>
<Cube>
<Name>Merchandise History</Name>
<ID>Merchandise History</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Cube>
<Cube>
<Name>POS Details</Name>
<ID>POS Details</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Cube>
<Cube>
<Name>Tenders</Name>
<ID>Tenders</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:30</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Unprocessed</State>
</Cube>
</Cubes>
<DataSources>
<DataSource xsi:type="RelationalDataSource">
<Name>RMIS_EUROSTOP</Name>
<ID>RMIS_EUROSTOP</ID>
<CreatedTimestamp>2006-07-13T10:21:29</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:29</LastSchemaUpdate>
</DataSource>
</DataSources>
<DataSourceViews>
<DataSourceView>
<Name>RMIS_EUROSTOP</Name>
<ID>RMIS_EUROSTOP</ID>
<CreatedTimestamp>2006-07-13T10:21:29</CreatedTimestamp>
<LastSchemaUpdate>2006-07-13T10:21:29</LastSchemaUpdate>
</DataSourceView>
</DataSourceViews>
<Roles>
<Role>
<Name>All</Name>
<ID>All</ID>
<CreatedTimestamp>2006-07-13T10:21:29</CreatedTimestamp>
<LastSchemaUpdate>2006-07-25T08:04:30</LastSchemaUpdate>
</Role>
</Roles>
<DatabasePermissions>
<DatabasePermission>
<Name>All</Name>
<ID>All</ID>
<CreatedTimestamp>2006-07-13T10:21:30</CreatedTimestamp>
<LastSchemaUpdate>2006-07-25T08:04:30</LastSchemaUpdate>
</DatabasePermission>
</DatabasePermissions>
</Database>
</xars:METADATA>
</row>
</root>
</return>
--=================================================
 
Status
Not open for further replies.

Similar threads

smak1
  • Locked
  • Question
add structure to XML
Replies
0
Views
690
smak1
smak1
Bijan Borazjani
  • Locked
  • Question
XML to PDF
Replies
0
Views
786
Bijan Borazjani
Bijan Borazjani
cdr_greg
  • Locked
  • Question
Display XML Line Breaks in Microsoft Word Content Controls
Replies
0
Views
1K
cdr_greg
cdr_greg
198709km
  • Locked
  • Question
Grouping XML key value using XSLT
Replies
1
Views
744
atlopes
atlopes
Sunil Sharma
  • Locked
  • Question
Add Minus (-) into pattern but constrain is Munis (-) should not be first character
Replies
1
Views
722
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top