piortasd (1/14/2013)
DECLARE @X XML
SELECT @X = CAST(x AS XML)
FROM OPENROWSET(
BULK 'C:\temp\teste.xml',
SINGLE_BLOB) AS T(x)
INSERT INTO OSUSR_DFP_PEP_ENTITIES (ENT_ID, NAME, FIRSTNAME, LASTNAME, PREFIX, SUFFIX,
AKA, NAMESOURCE, PARENTID, GOVDESIGNATION, ENTRYTYPE, ENTRYCATEGORY, ENTRYSUBCATEGORY,
ORGANIZATION, POSITIONS, REMARKS, DOB, POB, COUNTRY, EXPIRATIONDATE, EFFECTIVEDATE,
PICTUREFILE, LINKEDTO, RELATED_ID, SOURCEWEBLINK, TOUCHDATE, DIRECTID, PASSPORTID,
NATIONALID, OTHERID, DOB2, ENTLEVEL, MASTERID, WATCH, RELATIONSHIPS)
SELECT X.product.value('(Ent_ID/text())[1]', 'INT'),
X.product.value('(Name/text())[1]', 'nvarchar(1000)'),
X.product.value('(FirstName/text())[1]', 'nvarchar(500)'),
X.product.value('(LastName/text())[1]', 'nvarchar(500)'),
X.product.value('(Prefix/text())[1]', 'nvarchar(500)'),
X.product.value('(Suffix/text())[1]', 'nvarchar(500)'),
...
Well spotted! I hadn't looked closely enough to see the .query(...).value clauses, but good to know you have it solved 😀
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);