InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 04:12:57 GMT20http://www.sqlservercentral.com/Forums/Topic1391450-21-1.aspxNo problem....thanks for the feedbackMon, 03 Dec 2012 05:43:20 GMTarthurolcothttp://www.sqlservercentral.com/Forums/Topic1391450-21-1.aspxHi 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.Mon, 03 Dec 2012 05:37:55 GMTMichael F-386896http://www.sqlservercentral.com/Forums/Topic1391450-21-1.aspxHi,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[code="sql"]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);[/code]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.Sat, 01 Dec 2012 04:47:24 GMTarthurolcothttp://www.sqlservercentral.com/Forums/Topic1391450-21-1.aspxHello,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 [u]"Cross Apply[/u]" statement.As you can see I am not very experienced with this type of stuff - so yourhelp 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, MichaelFri, 30 Nov 2012 10:28:27 GMTMichael F-386896