We help IT Professionals succeed at work.

XML file read through T-SQL

Paula DiTallo
Paula DiTallo asked
on
Techies--
Between all the commenting and uncommenting I've done today, I've distilled what I've been working on down to the this code snippet. It does not bring back the <endpoint> record, but at least it sees <endpoints> and displays the column names. Can someone take a look at this and advise me?
-- CONTENTS OF THE XML FILE

-- <?xml version="1.0" encoding="ISO-8859-1" ?> 
-- <q1:endpoints xmlns:q1="http://metro.com/ssis">
--    <q1:endpoint>
--          <q1:server>paulad</q1:server>
--          <q1:version>10</q1:version>
--          <q1:schema>[SMA.OLAP]</q1:schema>
--          <q1:db>SiteManagement_staging</q1:db>
--    </q1:endpoint>
--    <q1:endpoint>
--          <q1:server>paulad</q1:server>
--          <q1:version>10</q1:version>
--          <q1:schema>[SMA.OLAP.DEF]</q1:schema>
--          <q1:db>SiteManagement_staging2</q1:db>
--    </q1:endpoint>
-- </q1:endpoints>

DECLARE @x xml;
SET @x = (SELECT * FROM OPENROWSET(BULK 'C:\Users\paulad\Desktop\DEF Reports\config\config.xml', SINGLE_BLOB) AS x);
 

WITH XMLNAMESPACES ('http://metro.com/ssis' AS q1)
SELECT
    T1.c1.value('@server[1]','varchar(20)') AS [server],
    T1.c1.value('@version[1]','char(2)') AS [version],
    T1.c1.value('@schema[1]','varchar(20)') AS [schema],
    T1.c1.value('@db[1]','varchar(50)') AS [db]
FROM
  @x.nodes('//q1:endpoints/q1:endpoint') AS T1(c1)

Open in new window

Comment
Watch Question

Commented:
Hi,

How about

DECLARE @x xml;
SET @x = (SELECT * FROM OPENROWSET(BULK 'C:\Users\paulad\Desktop\DEF Reports\config\config.xml', SINGLE_BLOB) AS x);
 
WITH XMLNAMESPACES ('http://metro.com/ssis' AS q1)
SELECT
    T1.c1.query('q1:server/text()') AS [server],
    T1.c1.query('q1:version/text()') AS [version],
    T1.c1.query('q1:schema/text()') AS [schema],
    T1.c1.query('q1:db/text()') AS [db]
FROM
  @x.nodes('//q1:endpoints/q1:endpoint') AS T1(c1)

Open in new window

Paula DiTalloIntegration developer

Author

Commented:

same results. Headers are there (e.g. server,version,schema,db) -- but no endpoint data.

Commented:
When I run this query I get:

server      version      schema      db
paulad      10      [SMA.OLAP]      SiteManagement_staging
paulad      10      [SMA.OLAP.DEF]      SiteManagement_staging2

What data are you missing? If you don't get any rows, check that the openrowset was successful. What is the result of select @x?

DECLARE @x xml;
SET @x = (SELECT * FROM OPENROWSET(BULK 'C:\Users\paulad\Desktop\DEF Reports\config\config.xml', SINGLE_BLOB) AS x);
 
select  @x;

Open in new window

Paula DiTalloIntegration developer

Author

Commented:
pivar--thanks so much, i forgot that i changed the namespace in the file--so it came back null. once i changed it to match all was well. thanks again for solving this.