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

import and shred xml file to sql 2008 table (openrowset) Expand / Collapse
Author
Message
Posted Friday, November 30, 2012 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 9, 2014 1:44 AM
Points: 3, Visits: 246
Hello,

I'm also trying to import certain fields of an xml-file into an existing sql table (neutc_import).
Problem is: I get '0 rows affected' when I execute the query below.
In the end I need to have mac-address and tcname for every machine in table neutc_import.

I'm sure the query is wrong at least regarding the "Cross Apply" statement.
As you can see I am not very experienced with this type of stuff - so your
help is really appreciated - maybe just a small adjustment necessary (?)

query:

INSERT INTO neutc_import(macaddr,tcname)
SELECT X.a.query('MACAddress').value('.', 'varchar(50)'),
X.a.query('ComputerName').value('.', 'VARCHAR(50)')
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK 'E:\scout_import\DSBU010995_Imdly_2_HwInfo.xml',
SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes('records/record') AS X(a);

referenced xml:

<?xml version="1.0" encoding="utf-8"?>
<Systems Version="4.00" xsi:schemaLocation="urn:schemas-ts-fujitsu-com:software-preload HWInfo4.00.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-ts-fujitsu-com:software-preload">
<System>
<SystemIdentification>
<SerialNumber>DSBU010995</SerialNumber>
<IndividualSoftwareID />
<SystemUUID>{B2E832CC-7484-11E1-8B14-5026909B9A32}</SystemUUID>
<CustomerSerialNumber />
<IMEINumber></IMEINumber>
<MACAddresses>
<MACAddress type="OnBoard">00:30:05:68:FF:BD</MACAddress>
</MACAddresses>
</SystemIdentification>
<OrderReferenceData>
<P82OrderNumber>xxxxxxx</P82OrderNumber>
<P82CustomerOrderNumber>xxxxxxx</P82CustomerOrderNumber>
<P84OrderNumber>xxxxxxx</P84OrderNumber>
<P84OrderPosition>100</P84OrderPosition>
<P84CustomerOrderNumber>xxxxxxx</P84CustomerOrderNumber>
<EndCustomerOrderNumber>xxxxxxx</EndCustomerOrderNumber>
</OrderReferenceData>
<ProductDetails>
<OrderCode>CUZ:KxxxVXXX-EMFxx</OrderCode>
<Product>LB P771 BLACK /CUZ:KxxxVXXX-EMFxx</Product>
</ProductDetails>
<SoftwareModules />
<OrderAttachements>
<HeaderSpecificData />
<PositionSpecificData>
<ClientParameters SubClient="None" Client="Caritas">
<ComputerName>CNxxxxx</ComputerName>
<OldComputerName>ONxxxxx</OldComputerName>
<PrimaryContact>Max Mustermann</PrimaryContact>
</ClientParameters>
</PositionSpecificData>
</OrderAttachements>
</System>
<System>
<SystemIdentification>
<SerialNumber>DSBU010995</SerialNumber>
<IndividualSoftwareID />
<SystemUUID>{B2E832CC-7484-11E1-8B14-5026909B9A32}</SystemUUID>
<CustomerSerialNumber />
<IMEINumber></IMEINumber>
<MACAddresses>
<MACAddress type="OnBoard">00:30:05:68:FF:BD</MACAddress>
</MACAddresses>
</SystemIdentification>
<OrderReferenceData>
<P82OrderNumber>xxxxxxx</P82OrderNumber>
<P82CustomerOrderNumber>xxxxxxx</P82CustomerOrderNumber>
<P84OrderNumber>xxxxxxx</P84OrderNumber>
<P84OrderPosition>200</P84OrderPosition>
<P84CustomerOrderNumber>xxxxxxx</P84CustomerOrderNumber>
<EndCustomerOrderNumber>xxxxxxx</EndCustomerOrderNumber>
</OrderReferenceData>
<ProductDetails>
<OrderCode>CUZ:KxxxVXXX-EMFxx</OrderCode>
<Product>LB P771 BLACK /CUZ:KxxxVXXX-EMFxx</Product>
</ProductDetails>
<SoftwareModules />
<OrderAttachements>
<HeaderSpecificData />
<PositionSpecificData>
<ClientParameters SubClient="None" Client="Caritas">
<ComputerName>CNxxxxx</ComputerName>
<OldComputerName>ONxxxxx</OldComputerName>
<PrimaryContact>Max Mustermann</PrimaryContact>
</ClientParameters>
</PositionSpecificData>
</OrderAttachements>
</System>
</Systems>

Greetings from nice munich, Michael
Post #1391450
Posted Saturday, December 1, 2012 4:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:07 AM
Points: 2,701, Visits: 1,694
Hi,
I think the main thing that you was missing was the namespace declaration. I have also made a couple of minor alterations, namely the cross apply and removing the .query() methods in the select statement. I have come up with the following that gets the MAC address and the computer name from the example xml

WITH xmlnamespaces (DEFAULT 'urn:schemas-ts-fujitsu-com:software-preload')
SELECT X.a.value('(SystemIdentification/MACAddresses/MACAddress/text())[1]', 'VARCHAR(30)') AS 'MAC',
X.a.value('(OrderAttachements/PositionSpecificData/ClientParameters/ComputerName/text())[1]', 'VARCHAR(30)')
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK 'd:\ExampleXml.xml',
SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes('Systems/System') AS X(a);


As I don't know the full schema of the XML, I wasn't sure if any of the nodes in the xpath repeat, but this should at least help get you on track.

Post #1391640
Posted Monday, December 3, 2012 5:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 9, 2014 1:44 AM
Points: 3, Visits: 246
Hi SSComitted,

... this worked like a charm - you really helped me out a lot here,
thanks again and have a good time !

greetings from Munich, Michael F.
Post #1391863
Posted Monday, December 3, 2012 5:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:07 AM
Points: 2,701, Visits: 1,694
No problem....thanks for the feedback
Post #1391866
Posted Sunday, June 9, 2013 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 5:07 AM
Points: 1, Visits: 2
+1 - BIG HELP. Thanks.
Post #1461335
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse