How to to Read a Xml from a Url Using T-Sql

How can I to read a XML from a URL using T-SQL?

To get the XML from a URL you need to do the following:

Enable Ole Automation Procedures

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Then to get the XML from a url, (answer based on an updated version from here), the following creates a temp table to store the value so you can then process the results using an xpath and substring.

This is a working example using a google maps xml, you will need to update the url and xpath to your specific requirements.

USE tempdb
GO

IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
CREATE TABLE #xml ( yourXML XML )
GO

DECLARE @URL VARCHAR(8000) 

DECLARE @QS varchar(50)

-- & or ? depending if there are other query strings
-- Use this for when there is other query strings:
SELECT @QS = '&date='+convert(varchar(25),getdate(),126)
-- Use this for when there is NO other query strings:
-- SELECT @QS = '?date='+convert(varchar(25),getdate(),126)
SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-65.598409&sensor=false'  + @QS

DECLARE @Response varchar(8000)
DECLARE @XML xml
DECLARE @Obj int 
DECLARE @Result int 
DECLARE @HTTPStatus int 
DECLARE @ErrorMsg varchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 

EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 

INSERT #xml ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 

SELECT  yourXML.value('(//GeocodeResponse/status)[1]','VARCHAR(MAX)') from #xml

In order to insert the substring you will need to do something like this to return everything after the pipe and add into your table:

INSERT tableDestination (valueDestination)
SELECT  substring(yourXML.value('(//response/comment)[1]','VARCHAR(MAX)'),charindex('|',yourXML.value('(//response/comment)[1]','VARCHAR(MAX)'),1)+1,len(yourXML.value('(//response/comment)','VARCHAR(MAX)'))) from #xml

Parsing XML fro URL using T-SQL

Try this:

DECLARE @XmlInput XML 

SET @XmlInput = '<List>
   <Data>
     <input1>1004519827</input1>
     <input2></input2>
   </Data>
   <Data>
     <input1>0214785698</input1>
     <input2></input2>
   </Data>
   <Data>
     <input1>1024589658</input1>
     <input2></input2>
   </Data>
</List>'

SELECT
    Input1 = XCol.value('(input1)[1]', 'bigint')
FROM
    @XmlInput.nodes('/List/Data') AS XList(XCol)

The .nodes() function will return a list of all matching XML elements (representing the <Data> XML elements), and then you can grab the <input1> value from those elements using the .value() XQuery method on them.

how can i read multiple xml values from URL through t-sql

For SQL Server, Use PIVOT to get the desired result .

DECLARE @xmlData AS XML

  SET @xmlData = CAST('<result>
  <count>2</count>
  <rows>
<row>
  <f>
    <n>id</n>
    <v>8557526</v>
  </f>
  <f>
    <n>vdb_id</n>
    <v>16239</v>
  </f>
  <f>
    <n>created</n>
    <v>2014-12-10T08:50:18</v>
  </f>
  <f>
    <n>task_id</n>
    <v>5755155</v>
  </f>
  <f>
    <n>process_id</n>
    <v />
  </f>
  <f>
    <n>update_comments</n>
    <v />
  </f>
</row>
<row>
  <f>
    <n>id</n>
    <v>8567425</v>
  </f>
  <f>
    <n>vdb_id</n>
    <v>16239</v>
  </f>
  <f>
    <n>created</n>
    <v>2014-12-11T00:23:59</v>
  </f>
  <f>
    <n>task_id</n>
    <v>5755155</v>
  </f>
  <f>
    <n>process_id</n>
    <v />
  </f>        
  <f>
    <n>update_comments</n>
    <v />
  </f>
</row>
</rows>
</result>' AS XML)

SELECT Piv.Id, piv.[vdb_id], piv.[created], piv.[task_id], piv.[update_comments] 
FROM 
(
    SELECT Result1.value('v[1]','VARCHAR(200)') AS A, 
           Result1.value('n[1]','VARCHAR(200)') AS B,
         DENSE_RANK() over(order by Result) AS Num
    FROM @xmlData.nodes('//result/rows/row') xmlData(Result) 
    CROSS APPLY xmlData.Result.nodes('./f') xmlData1(Result1)
) AS A    
Pivot (Min(A) FOR B IN ([id],
                        [vdb_id],
                        [created],
                        [task_id],
                        [update_comments])
       ) piv

T-SQL Read xml file with namespaces

Use WITH XMLNAMESPACES to declare the namespaces and use the respective prefix for nodes not in the default namespace.

...

-- Parse the XML in to the temp table declared above
;WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/',
                     'http://schemas.datacontract.org/2004/07/Sistema.Soap.Contracts' as a,
                     'http://www.w3.org/2001/XMLSchema-instance' as i
                     )
INSERT
INTO    @ParsingTable
    (Id_Value)
SELECT  xmlData.A.value('.', 'INT') AS Id_Value
FROM    @XMLToParse.nodes('Response/AuthResult/a:Result') xmlData(A)

...

Read XML data into SQL Server table

Well, your XML-query is fine, although I'd simplify it a bit:

DECLARE @xml XML=
'<?xml version="1.0" encoding="utf-8"?>
<data>
  <kitap>
    <Adi>Matematik +5 Yaş</Adi>
    <Barkod>9786052342046</Barkod>
    <Resim>http://xxxx.com.tr/Icerik/Gorsel/Urun/9786052342046.jpg</Resim>
  </kitap>
  <kitap>
    <Adi>Broke - Light (Ciltli)</Adi>
    <Barkod>9786057944085</Barkod>
    <Resim>http://xxx.com.tr/Icerik/Gorsel/Urun/9786057944085.jpg</Resim>
  </kitap>
</data>';

SELECT 
    X.kitap.value('(Adi/text())[1]', 'nvarchar(1000)'),
    X.kitap.value('(Barkod/text())[1]', 'nvarchar(1000)'),
    X.kitap.value('(Resim/text())[1]', 'nvarchar(1000)')
FROM @xml.nodes('data/kitap') AS X(kitap);

The reason for the error mentioned ("String or binary data would be truncated") is with the length of your target fields assumably. You are reading all three columns as nvarchar(1000). Use the following to find the max lengths per column:

SELECT 
    MAX(LEN(X.kitap.value('(Adi/text())[1]', 'nvarchar(max)'))) AS MaxLenAdi,
    MAX(LEN(X.kitap.value('(Barkod/text())[1]', 'nvarchar(max)'))) AS MaxLenBarkod,
    MAX(LEN(X.kitap.value('(Resim/text())[1]', 'nvarchar(max)'))) AS MaxLenResim
FROM @xml.nodes('data/kitap') AS X(kitap);

Now check your ddd table's definition, if the values will fit into 1) nvarchar(1000) and 2) into the table's columns.

One more thing to mention: Your XML-file is claiming to be utf-8 encoded. Reading this simply as SINGLE_BLOB might be dangerous... This will call the file as a byte-stream and will take each single byte as a character. But utf-8 uses multi-byte codes to encode all the characters existing in the world. This will either lead to garbage data or to an error.

• Try to import this as SINGLE_CLOB (Character LOB - not secure, but a bit better)
• Try to import this with utf-8 support (I think this is available since v2014 SP1)
• Use an external tool to convert your file to utf-16 (even better: ucs-2)

And one final hint: Very often XML-files claim to have a special encoding (first line declaration <?xml ... encoding="xyz"?>. Very often people just do not know what this is and think, that this - hoewever - must be there ("Uhm... Don't know... Just copied the template..."). It might be worth to check the file's actual encoding. In general it is a good advise to omit this declaration entirely within SQL-Server.

How to parse XML file in a folder with dynamic t-sql?

It is much easier to implement in SQL Server 2017 and later. It has much better API to deal with the file system.

Please try the following solution. It will work in SQL Server 2012.

I modified the StartTime column data type as TIME(0).

You would need to modify @folder variable value to match what you have in your environment.

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.XMLTESTTABLE;

CREATE TABLE dbo.XMLTESTTABLE
(
    PON varchar(10), 
    ASP int, 
    LTN varchar(11),   
    GAS int, 
    QY varchar(15), 
    LNO varchar(2), 
    StartTime TIME(0)
);

DECLARE @xml XML
   , @sql NVARCHAR(MAX)
   , @XMLfileName VARCHAR(256) -- 'e:\Temp\TradeFeed\PT38.xml';
   , @folder VARCHAR(256) = 'e:\Temp\TradeFeed';

DECLARE @tbl TABLE (
    id INT IDENTITY(1,1) PRIMARY KEY,
    [fileName] VARCHAR(512),
    depth INT,
    isfile BIT
);

INSERT INTO @tbl ([fileName], depth, isfile)
EXEC master.sys.xp_dirtree @folder,1,1;

-- just to see
SELECT * FROM @tbl;

-- filter out not need files
SELECT TOP(1) @XMLfileName = CONCAT(@folder, '\', [fileName])
FROM @tbl
WHERE isfile = 1
    AND [fileName] LIKE '%.xml';

SET @sql = N'SELECT @xmlOut = XmlDoc FROM OPENROWSET (BULK ' + QUOTENAME(@XMLfileName,NCHAR(39)) + ', SINGLE_BLOB) AS Tab(XmlDoc)';

EXEC master.sys.sp_executesql @sql, N'@xmlOut XML OUTPUT', @xmlOut = @xml OUTPUT;

INSERT INTO XMLTESTTABLE(PON, ASP, LTN, GAS, QY, LNO, StartTime)
SELECT @xml.value('(/MOD1/DC/DC4/TABNAM/text())[1]', 'VARCHAR(10)')
    , c.value('(CHARG/text())[1]', 'int')
    , c.value('(PLN_ORDER/text())[1]', 'VARCHAR(10)')
    , c.value('(CHARG/text())[1]', 'int')
    , c.value('(QTY/text())[1]', 'DECIMAL(10,0)') AS [qty]
    , RIGHT(c.value('(LNO/text())[1]', 'VARCHAR(10)'), 2) AS [LNO]
    , TRY_CAST(STUFF(STUFF(c.value('(STR2/text())[1]', 'CHAR(6)'),3,0,':'),6,0,':') AS TIME(0))
FROM @xml.nodes('/MOD1/DC/ZPR') AS t(c);

-- test
SELECT * FROM dbo.XMLTESTTABLE;

Output

+-----------+---------+------------+---------+------+-----+-----------+
|    PON    |   ASP   |    LTN     |   GAS   |  QY  | LNO | StartTime |
+-----------+---------+------------+---------+------+-----+-----------+
|    DC4    | 6186588 | 6963701111 | 6186588 | 4166 |  01 | 21:16:09  |
+-----------+---------+------------+---------+------+-----+-----------+

---

Related Topics