SQL SERVER – Simple Example of Reading XML File Using T-SQL

In one of the previous article we have seen how we can create XML file using SELECT statement SQL SERVER – Simple Example of Creating XML File Using T-SQL. Today we will see how we can read the XML file using the SELECT statement.

Following is the XML which we will read using T-SQL:

Following is the T-SQL script which we will be used to read the XML:

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'

SELECT
a.b.value(‘Colors[1]/Color1[1]‘,‘varchar(10)’) AS Color1,
a.b.value(‘Colors[1]/Color2[1]‘,‘varchar(10)’) AS Color2,
a.b.value(‘Colors[1]/Color3[1]‘,‘varchar(10)’) AS Color3,
a.b.value(‘Colors[1]/Color4[1]/@Special’,‘varchar(10)’)+‘ ‘+
+
a.b.value(‘Colors[1]/Color4[1]‘,‘varchar(10)’) AS Color4,
a.b.value(‘Colors[1]/Color5[1]‘,‘varchar(10)’) AS Color5,
a.b.value(‘Fruits[1]/Fruits1[1]‘,‘varchar(10)’) AS Fruits1,
a.b.value(‘Fruits[1]/Fruits2[1]‘,‘varchar(10)’) AS Fruits2,
a.b.value(‘Fruits[1]/Fruits3[1]‘,‘varchar(10)’) AS Fruits3,
a.b.value(‘Fruits[1]/Fruits4[1]‘,‘varchar(10)’) AS Fruits4
FROM @MyXML.nodes(‘SampleXML’) a(b)

Please note in above T-SQL statement XML attributes is read the same as XML Value.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

84 thoughts on “SQL SERVER – Simple Example of Reading XML File Using T-SQL

  1. Pingback: SQL SERVER - Simple Example of Creating XML File Using T-SQL Journey to SQL Authority with Pinal Dave

    • Thanks Pinal , really a nice solution, but i have an other issue that i have a large data and want to create its xml file but when i excutes above ur mentioned query i get the a small piece of results(approx 2k characters), please can you halp me out
      Thanks

    • Hi Sir,

      I have XML that is in the Format

      contractTypeId
      15

      endDate
      J

      startDate
      03/01/2004

      restrictedService
      False

      requestType
      R

      requestTypeId
      4

      contractAmount
      $10.00

      versionNo
      1

      listId
      249

      changeOrderId
      -1

      supplementNo
      0

      templateId
      19

      requestDocId
      224

      requestId
      234

      orgUnitTemplateId
      19

      contractTypeRequired
      False

      EndDate
      31/03/2011

      it’s required for me to update the Only startdate and EndDate values with new dates. Please help me on this ?

    • sir I need help for inserting muliple rows in table
      for 100 colours then I have to folowing
      a.b.value(‘Colors[1]/Color1[1]‘,‘varchar(10)’) AS Color1,
      a.b.value(‘Colors[1]/Color2[1]‘,‘varchar(10)’) AS Color2,
      ……..
      ……
      …….
      a.b.value(‘Colors[1]/Color1[100]‘,‘varchar(10)’) AS Color1,
      this much code we have.we have any option to write in 2 or 3 lines
      pls help me .mail me on [email removed]

  2. Hello sir,

    The same is occured here. How to rectify it.

    Thanks
    Puneet

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘‘’.
    Msg 102, Level 15, State 1, Line 19
    Incorrect syntax near ‘‘’.

  3. Hi Pinal,

    Along with the ‘nodes’ funtion we do have OpenXML in SQL Server for reading the XML data.
    So what is the difference between these two or which one is better in terms of performance?

    -Munish Bansal

  4. Dear Pinal
    Please tell me how to read multiple rows. like

    DECLARE @MyXML XML
    SET @MyXML = ‘

    White
    Blue
    Black
    Green
    Red

    Orange
    Yello
    Pink
    Purple
    Indigo

    Apple
    Pineapple
    Grapes
    Melon

    SELECT
    a.b.value(‘Colors[1]/Color1[1]‘,’varchar(10)’) AS Color1,
    a.b.value(‘Colors[1]/Color2[1]‘,’varchar(10)’) AS Color2,
    a.b.value(‘Colors[1]/Color3[1]‘,’varchar(10)’) AS Color3,
    a.b.value(‘Colors[1]/Color4[1]/@Special’,’varchar(10)’)+’ ‘+
    +a.b.value(‘Colors[1]/Color4[1]‘,’varchar(10)’) AS Color4,
    a.b.value(‘Colors[1]/Color5[1]‘,’varchar(10)’) AS Color5,
    a.b.value(‘Fruits[1]/Fruits1[1]‘,’varchar(10)’) AS Fruits1,
    a.b.value(‘Fruits[1]/Fruits2[1]‘,’varchar(10)’) AS Fruits2,
    a.b.value(‘Fruits[1]/Fruits3[1]‘,’varchar(10)’) AS Fruits3,
    a.b.value(‘Fruits[1]/Fruits4[1]‘,’varchar(10)’) AS Fruits4
    FROM @MyXML.nodes(‘SampleXML’) a(b)
    UNION ALL
    SELECT
    a.b.value(‘Colors[2]/Color1[1]‘,’varchar(10)’) AS Color1,
    a.b.value(‘Colors[2]/Color2[1]‘,’varchar(10)’) AS Color2,
    a.b.value(‘Colors[2]/Color3[1]‘,’varchar(10)’) AS Color3,
    a.b.value(‘Colors[2]/Color4[1]‘,’varchar(10)’) AS Color4,
    a.b.value(‘Colors[2]/Color5[1]‘,’varchar(10)’) AS Color5,
    ” AS Fruits1,
    ” AS Fruits2,
    ” AS Fruits3,
    ” AS Fruits4
    FROM @MyXML.nodes(‘SampleXML’) a(b)

    Is this correct way? how i can handle if i have 100 or many rows?

    Thanks & Regards
    Smita

    • DECLARE @productIds xml
      SET @productIds =’3615′

      SELECT
      ParamValues.ID.value(‘.’,’VARCHAR(20)’)
      FROM @productIds.nodes(‘/Products/id’) as ParamValues(ID)

  5. >>Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘‘’.

    This is a text encoding issue with Pinal’s examples. The ‘ character is encoded incorrectly for SSMS. You need to change the quotes in Pinal’s posts from ’ to ‘ and from ” to “. At least if you are using the 1033 codepage — not sure otherwise.

  6. You could also do this if you just want all the values back:

    SELECT @MyXML.query(
    ‘for $x in //Colors/*
    return data($x)’)
    UNION ALL
    SELECT @MyXML.query(
    ‘for $x in //Fruits/*
    return data($x)’);

    If you want to put the xml values in columns, I usually do it like this:

    SELECT
    Color1 = T.item.value(‘(Color1)[1]‘, ‘CHAR(10)’)
    FROM @MyXML.nodes(‘SampleXML/Colors’) AS T(item);

    Or like this:

    SELECT
    Color1 = T.item.query(‘Color1′).value(‘.’, ‘CHAR(10)’)
    FROM @MyXML.nodes(‘SampleXML/Colors’) AS T(item);

  7. hi ..
    can u please help me to solve my probleme
    i am studying SQL in relation with XML and i must imoprt a XML file to SQL table .. can u help me in that …

  8. hello sir,

    can i load table attibute with its value in xml file using sql.
    if yes pls give me example query.

  9. Hi,

    I would like to read an xml and get a value from it but the xml file is returned via the following url:

    http://maps.google.com/maps/geo?q=MyPostalCode&output=xml&key=MyGoogleMapAPI_Key

    where MyPostalCode is a postal code
    MyGoogleMapAPI_Key is my google Map Api key that I received when registered.

    To read the xml file, I used the folowing code :

    select * FROM OPENROWSET(BULK ‘http://maps.google.com/maps/geo?q=MyPostalCode&output=xml&key=MyGoogleMapAPI_Key’,SINGLE_BLOB) as c

    but I am getting the following error:

    Cannot bulk load because the file… could not be opened. Operating system error code 123(error not found).

    Do you know how to fix this issue?

  10. @Benn,

    If you are using SQL 2005 and above version, then you can get values by this query:

    DECLARE @XML XML
    SET @XML = ‘

    MyPostalCode

    602
    geocode

    SELECT d.v.value(‘../name[1]‘,’VARCHAR(MAX)’),
    d.v.value(‘code[1]‘,’VARCHAR(MAX)’),
    d.v.value(‘request[1]‘,’VARCHAR(MAX)’)
    FROM @XML.nodes(‘/kml/Response/Status’) d(v)

    Let me know if it helps you.

    Thanks,

    Tejas
    SQLYoga.com

  11. @Benn,

    You just need to pass the XML you get from Google API to DB.

    I did the same, I manually copied xml and give you query.

    You can enter httpMyGoogleMapAPI_Key in any element where you want. You can access it from SQL.

    Tejas
    SQLYoga.com

  12. Hi Tejas,

    Well… ok… my descrition was not complet. I’ll try to give you all requested information to help me fine the solution.

    I already have a table in my database let’s say T_RESTO with the following fields:
    T_RESTO(Name,Address, PostalCode) let’s keep it like that.

    and I have an interface where a user can enter a postalcode to fine Resto near the given postal code.

    I would like to create a T-Sql function that will have one parameter, the PostalCode entered by a user, and the function should returned a table with the following fields:
    - Resto’s Name
    - Address
    - PostalCode
    - Distance between the Resto’s postal code and the postal code entered by user.

    My point was to start creating a T-Sql that will returned the coordinates for a given PostalCode. These coordinates will be taken from Google Maps API.

    Does this make sens to you?
    Benn

  13. Multi line selection:

    DECLARE @MyXML XML
    SET @MyXML = ‘

    White
    Blue
    Black
    Green
    Red

    White1
    Blue1
    Black1
    Green1
    Red1

    White2
    Blue2
    Black2
    Green2
    Red2

    SELECT
    a.b.value(‘Color1[1]‘,’varchar(10)’) AS Color1,
    a.b.value(‘Color2[1]‘,’varchar(10)’) AS Color2,
    a.b.value(‘Color3[1]‘,’varchar(10)’) AS Color3,
    a.b.value(‘Color4[1]/@Special’,’varchar(10)’)+’ ‘+ a.b.value(‘Color4[1]‘,’varchar(10)’) AS Color4,
    a.b.value(‘Color5[1]‘,’varchar(10)’) AS Color5
    FROM @MyXML.nodes(‘SampleXML/Colors’) a(b)

  14. Hi Tejas,

    DECLARE @XML XML
    SET @XML = ‘

    MyPostalCode

    SELECT d.v.value(‘../name[1]‘,’VARCHAR(MAX)’),
    d.v.value(‘code[1]‘,’VARCHAR(MAX)’),
    d.v.value(‘request[1]‘,’VARCHAR(MAX)’)
    FROM @XML.nodes(‘/kml/Response/Status’) d(v)

    select * FROM OPENROWSET(BULK ‘http://maps.google.com/maps/geo?q=MyPostalCode&output=xml&key=MyGoogleMapAPI_Key’,SINGLE_BLOB) as c

    This gif me error

    (0 row(s) affected)
    Msg 4861, Level 16, State 1, Line 12
    Cannot bulk load because the file “http://maps.google.com/maps/geo?q=MyPostalCode&output=xml&key=MyGoogleMapAPI_Key” could not be opened. Operating system error code 123(error not found).

  15. hi , anyone tell me when we have to define for xml reading value through this code then i am getting error in xmlrd.Read(); this position.

    example:–

    XmlTextReader xmlrd = new XmlTextReader(“c:\\books1.xml”);
    xmlrd.Read();
    while (xmlrd.Read())
    {
    string val = xmlrd.Read().ToString();
    //string val1 = xmlrd.Name;
    xmlrd.Close();
    }

  16. Hi,
    I have excel data base,want to create this database into xml format and import this xml data into sql server enterprise edition.

    how can i do this.

    pls explian with screen shots

    its very usefull for my all friends

  17. Dear sir,
    Pls explain in detail about to install sql server 2005 enterprise edition on ubuntu.

    pls tell me the procedure in step by step

    thanks

    vinoth

  18. Hello Pinal,
    I am trying to OPENROWSET (BULK…) a XML file. It works except my server takes 5 mins to load a 18MB file. I have a dedicated server, SQL 2005 express sp3.

    Should it take this long? All the research I have read, people are reporting over 100MB files loading in less than 1 min.

    I didnt want to post my script here, its kinda long but would be glad to send it to you, or post.

    Thank you for help!

    Jason

  19. Hi…We are using OpenXML in one of our SPs….We took reference form following code given in some website…

    CREATE PROCEDURE update_authors_OPENXML (
    @xml_text TEXT)

    AS
    SET NOCOUNT ON
    – document handle:
    DECLARE @i INT

    – create internal representation
    – of the document
    EXEC sp_xml_preparedocument @i OUTPUT, @xml_text

    – run an update:
    UPDATE authors
    SET au_lname = b.au_lname,
    au_fname = b.au_fname,
    address = b.address,
    city = b.city,
    state = b.state,
    zip = b.zip,
    contract = b.contract
    FROM authors a INNER JOIN
    OPENXML(@i, ‘/root/authors’)
    WITH authors b
    ON a.au_id = b.au_id

    But we are facing while using above syntax with our table….because the Column which we are using for Inner Join (a.au_id = b.au_id as in above case) is Identity column….and the above syntax gives error…It does not select the Identity column from the table….Instead it shows ‘Invalid Column name’ error.

    Can you suggests what is wrong ….or any other way through which we can get the Identity column with above code….

    When I removed the Identity property from the table and tested then it works fine…but the issue is with Identity property….

    I would be very grateful to you if you could provide any help regarding this issue….

    Kinjal Shah

  20. Hi Pinal Dave,

    I would like to insert a xml file into a table using queries and then want to read values inside the xml file. How can i do it, Please provide me a sample example so that i can able to learn it.

    Thanks

    Regards
    Ganesh.

  21. Hi,

    We are planning to shift our enterprise application to windows azure platform…

    but we have critical issue where we are using XMLNAMESPACES in attribute level … since sql azure is not supporting this syntax what can be the best alternative or will it be supported in future sql azure features…..

    this is business based enterprise application developed based on client requirements we cannot change the xml format.. so please provide best alternative ..please see the sample below

    DECLARE @x xml ,
    @L_C VARCHAR(10),
    @l_cnt numeric(10),
    @doc int
    BEGIN
    SET @x=’

    ;WITH XMLNAMESPACES (‘your:namespace’ as “CT”)
    SELECT c.value(‘@CT:I’,’NUMERIC(10)’) AS CT_I,
    c.value(‘@C’,’NUMERIC(10)’) AS C FROM @x.nodes(‘//F’) AS T(c)
    END

  22. –Create Parent Table
    CREATE TABLE [dbo].[Parent]
    (
    [Parent_ID] [int] IDENTITY(1,1) primary key clustered,
    [InternalID] [int] NULL,
    [Action] [nvarchar](255) NULL,
    [InsertUserID] [nvarchar](255) NULL,
    [InsertDateTime] [nvarchar](255) NULL,
    [UpdateUserID] [nvarchar](255) NULL
    )
    –Create Child Table
    CREATE TABLE [dbo].[Child]
    (
    [Child_ID] [int] IDENTITY(1,1) primary key clustered,
    [Parent_ID] int references Parent(Parent_ID),
    [InternalID] [int] NULL,
    [Action] [nvarchar](255) NULL,
    [InsertUserID] [nvarchar](255) NULL,
    [InsertDateTime] [nvarchar](255) NULL,
    [UpdateUserID] [nvarchar](255) NULL
    )

    –Create KidTable
    CREATE TABLE [dbo].[Kid]
    (
    [Kid_ID] [int] IDENTITY(1,1) primary key clustered,
    [Child_ID] int references Child(Child_ID),
    [InternalID] [int] NULL,
    [Action] [nvarchar](255) NULL,
    [InsertUserID] [nvarchar](255) NULL,
    [InsertDateTime] [nvarchar](255) NULL,
    [UpdateUserID] [nvarchar](255) NULL
    )

    DECLARE @hDoc int

    EXEC sp_xml_preparedocument @hDoc OUTPUT,

    ASDZXCCFG
    08-29-2008 17:13:57
    SYSTEM_USER
    13-05-2010 13:22:41

    Child1
    02-20-2008 16:27:54
    SYSTEM_USER
    11-05-2009 15:22:44

    Kid1
    02-20-2008 16:27:54
    CATS
    02-22-2008 12:12:43

    KId2
    02-20-2008 16:27:54
    CATS
    02-22-2008 12:12:43

    Child2
    08-20-2008 16:27:54
    SYSTEM_USER
    11-05-2009 15:22:44

    Kid3
    02-20-2008 16:27:54
    CATS
    02-22-2008 12:12:43

    —–Insert into Parent Table
    Insert into Parent
    SELECT *
    FROM OPENXML(@hDoc, ‘/Parent’,3)
    WITH (
    [InternalID] [int],
    [Action] varchar(10),
    [InsertUserID] varchar(10),
    [InsertDateTime] varchar(10),
    [UpdateUserID] varchar(10))
    —————————-
    Declare @Parent_ID INT
    SELECT @Parent_ID = @@IDENTITY
    ————————-
    –Insert into Child table
    Insert into Child
    select @Parent_ID,*
    FROM OPENXML(@hDoc, ‘/Parent/Childs/Child’,3)
    WITH (
    [InternalID] [int],
    [Action] varchar(10),
    [InsertUserID] varchar(10),
    [InsertDateTime] varchar(10),
    [UpdateUserID] varchar(10))

    declare @Child_ID int
    select @Child_ID = @@identity

    –Insert into Request table

    Insert into Kid
    select @Child_ID,*
    FROM OPENXML(@hDoc, ‘/Parent/Childs/Child/Kids/Kid’,3)
    WITH (
    [InternalID] [int],
    [Action] varchar(10),
    [InsertUserID] varchar(10),
    [InsertDateTime] varchar(10),
    [UpdateUserID] varchar(10))

    ——————————————————
    select * from Parent
    select * from Child
    select * from Kid

    I am trying to load the XML data into SQL server table.

    Below is the structure for my table I have Identity column in each table and I want to pass that Identity value to the subnode. But when I am trying to load the third level(Kid table) , I am just getting the last Identity value from the Child Table into the Kid table.

    I need Child_ID 1 for InsertUserID(Kid1,Kid2) and Child_ID 2 for InsertUserID(Kid3) in the Child Table.

    Is there any way that i can get the populate the one row in Child table and get the identity of that and populate the Kid table associate with that Child_ID , and again same for the second row in the Child table.

    —Below is the Expected result for the Kid Table
    Kid_ID Child_ID InternalID Action InsertUserID InsertDateTime UpdateUserID
    1 1 3406436 Insert Kid1 02-20-2008 CATS
    2 1 3406437 Update KId2 02-20-2008 CATS
    3 2 3406438 None Kid3 02-20-2008 CATS

    –But I am getting the thisChild_ID 2 for all the records in Kid Table)

    Kid_ID Child_ID InternalID Action InsertUserID InsertDateTime UpdateUserID
    1 2 3406436 Insert Kid1 02-20-2008 CATS
    2 2 3406437 Update KId2 02-20-2008 CATS
    3 2 3406438 None Kid3 02-20-2008 CATS

  23. Hello Pinal, I need a help.

    I have a table using SQL2k5 and in one table I have a column xml tagged like

    ‘JXY1XY1ABC INSURANCE

    I would like to strip the columns from “ID” onwards in a variable so that I can use it in a differet query. Could you please help me out, I am unable to get the ID value.

    Tx
    Ram

  24. Sir I have this XML structure:

    Declare @xmlData XML = ‘

    ABC
    DEF
    GHI
    JKL
    MNO
    PQR

    SELECT d.c.value(‘StudyDesign[1]‘,’nvarchar(MAX)’) as StudyDesign
    FROM @xmlData.nodes(‘/Study/Study_Design’) d(c)

    This just returns me the 1st node data. I want all the nodes of StudyDesign in the table.

    Please assist me out of this problem.

    With Regards

  25. Hi Niladri,

    To achive this you will have to use text()[1] property and one more thing is you will have to give full path till your child node just like following

    SELECT d.c.value(‘text()[1]‘,’nvarchar(MAX)’) as StudyDesign
    FROM @xmlData.nodes(‘/Study/Study_Design/StudyDesign’) d(c)

    you can see your StudyDesign is child node and i have removed it and put it in path

    i hope you got it…

    Thanks

  26. Sorry, the xml did not get pasted properly earlier

    HEADER1

    HEADER2

    HEADER3

    HEADER4

    rank1
    rank2
    Value1
    rank3
    Value2
    rank4
    Value3

  27. I need your help to get this query to output like:

    DECLARE @xmlDoc xml

    SET @xmlDoc = ‘

    '42-211'
    '42-211- – '

    select
    a.b.value(‘Old[1]‘,’varchar(20)’) as old,
    a.b.value(‘New[1]‘,’varchar(20)’) as new
    from @xmldoc.nodes(‘well’)a(b)

    Output:
    Column Name Old New

  28. I need your help to get the query below to output like below. Also, the CoumnName will change daily, how do I get it to pick it up automatically?

    DECLARE @xmlDoc xml

    SET @xmlDoc = ‘

    '42-211'
    '42-211- – '

    select
    a.b.value(‘Old[1]‘,’varchar(20)’) as old,
    a.b.value(‘New[1]‘,’varchar(20)’) as new
    from @xmldoc.nodes(‘well’)a(b)

    Output:
    ColumnName OLDVALUE NEWVALUE
    data data data

    Please help!

  29. Hello there,

    I have a XML column. When I query the table I see the column values as AAEAAAD/////AQAAAAA…………….. and so on.

    Is there a way I can read the actual XML file stored. How do I get it. I do not know the schema of nodes in the XML to query as given initially in this blog.

    Any help is much appreciated.

    Thank you,
    Suchi

  30. hi sir,

    while i am getting values from database to xml file using sqlserver, am able to getting only one record information.

    if i want get all records information wat i have to do..
    Pls give me such kind of suggestion

  31. xml source file: = c:\products.xml

    reads:

    1
    Book

    2
    DVD

    3
    Video

    my sql code is:

    DECLARE @MyXML XML
    SET @MyXML = ‘c:\products.xml’

    SELECT
    a.b.value(‘sku[1]/sku[1]‘,’varchar(255)’) as game
    FROM @MyXML.nodes(‘Product’) a(b)

    Result = No Records

    I’m hoping to find an answer here, I’ve tried multiple approaches. As a small aside, is there any way sql can read the remote xml file from the web, rather then only reading local versions? I’m crawling the data right now, but it would be easier to use the url in code rather then bring the xml in locally.

  32. Products > Product > SKU are the node levels.

    Sorry forgot the xml would parse, here’s another try at
    showing the original file:

    1
    Book

    2
    DVD

    3
    Video

  33. Hi

    Is there any way to call the API in sql server. (API returns XML data). if it is possible please suggest some sample.

    Thanks
    Manikandan

  34. – SQL Server 2008 execution – xquery
    DECLARE @xml xml = ‘
    Host=ms0001xp/Pid=1400
    mani
    Thanigaimani

    SELECT V.col.query(‘UserId’).value(‘.’, ‘VARCHAR(32)’) AS UserID,
    V.col.query(‘UserName’).value(‘.’, ‘VARCHAR(32)’) AS UserName
    FROM @XML.nodes(‘StudyDeleteExtendedInfo’) AS V(col);

  35. How to read a chinese text from XML in SQL server 2008

    declare @xml_ xml
    set @xml_ =’false100504測試合同 ÓPCM üñ ¿¡’
    SELECT
    OPCM_ALTERNATIVE_NAME = T.item.value(N’(Name)[1]‘, ‘NCHAR(100)’)
    FROM @XML_.nodes(‘/Event/Body/Message’) AS T(item)

    i am getting value ???? ÓPCM ñ¿¡ instead of 測試合同 ÓPCM üñ ¿¡

    Please help to find the solution

  36. Hello Sir,

    My column datatype is nvarchar(MAX) and it stores the value:
    Closed

    Could you please help me how I write the sql query to get only en-US value from the column?

    Thanks in anticipation.

    Ragards,
    Sanjay

  37. please can somebody tell me how to read this xml file in sql server..from the starting..i am a beginner..

    i need the code urgently!

    OSS_SERVERS
    DLGGN01

    vsDataMeContext
    EricssonSpecificAttributes.10.22

    AEGIS2
    10.155.63.92
    vM.1.31
    SYNCHRONISED
    213
    false

    Ericsson
    AEGIS2
    RBS
    CXP9014346/1_R5AV/14
    SubNetwork=ONRM_RootMo_R,ManagementNode=ONRM

    vsDataManagedElement
    EricssonSpecificAttributes.10.22

    SubNetwork=ONRM_RootMo_R,Site=RNC
    RBS3418

  38. OSS_SERVERS
    DLGGN01

    vsDataMeContext
    EricssonSpecificAttributes.10.22

    AEGIS2
    10.155.63.92
    vM.1.31
    SYNCHRONISED
    213
    false

    Ericsson
    AEGIS2
    RBS
    CXP9014346/1_R5AV/14
    SubNetwork=ONRM_RootMo_R,ManagementNode=ONRM

    vsDataManagedElement
    EricssonSpecificAttributes.10.22

    SubNetwork=ONRM_RootMo_R,Site=RNC
    RBS3418

  39. Below is the sample xml file
    ———————————————————————————–

    - – 27/06/2011 123456789 7372 CCODE001 CUSTOMER NAME N – REC ADDR 1 REC ADDR 2 WORLD SENDER NAME – SEND ADDR 1 SEND ADDR 2 SEND ADDR 3 STATE POSTCODE S CARRIER NAME 999123 BY ROAD N – 6 18 0 0 0 N 10.00 11.00 0 11.00 11.00 0 11.00 11.00 1 N N – – -ABCDEFGHIJ000000990001 -ABCDEFGHIJ000000990002 -ABCDEFGHIJ000000990003 -ABCDEFGHIJ000000990004 -ABCDEFGHIJ000000990005 -ABCDEFGHIJ000000990006 0 – T000123 CUSTOMER 6 6 – – – –
    ————————————————————————————————
    I am importing the dat into temp table. and 1st script works fine.

    CREATE TABLE dbo.[#tmp_xml_data](
    [ref] [varchar](100),
    [cdesc] [varchar](15),
    [cno] [varchar](14),
    [cdate] [varchar](10),
    [cname] [varchar](50))

    DECLARE @c xml
    SELECT @c = BulkColumn FROM OPENROWSET(BULK ‘C:\imports\sample.xml’, SINGLE_BLOB) AS A

    INSERT INTO [#tmp_xml_data]
    SELECT Details.value(‘Ref[1]‘,’VARCHAR(100)’),
    Details.value(‘Desc[1]‘,’VARCHAR(15)’),
    T.C.value(‘ConNo[1]‘,’VARCHAR(14)’),
    T.C.value(‘ConDate[1]‘,’VARCHAR(10)’),
    T.C.value(‘CarrierName[1]‘,’VARCHAR(50)’)
    FROM @c.nodes(‘MyImportFile/Connote’) AS T(C)
    CROSS APPLY @c.nodes(‘MyImportFile/Connote/RecAddr/FreightLineDetails’) AS T2(Details)

    This works fine and I get the data in to my temp table

    however when I modify the script (see below) to get the data from more tags then it does not works. Could you please help.

    CREATE TABLE dbo.[#tmp_xml_data](
    [ref] [varchar](100),
    [cdesc] [varchar](15),
    [cno] [varchar](14),
    [cdate] [varchar](10),
    [add1] [varchar](50),
    [add2] [varchar](50),
    [add3] [varchar](50),
    [cname] [varchar](50))

    DECLARE @c xml
    SELECT @c = BulkColumn FROM OPENROWSET(BULK ‘C:\imports\sample.xml’, SINGLE_BLOB) AS A

    INSERT INTO [#tmp_xml_data]
    SELECT Details.value(‘Ref[1]‘,’VARCHAR(100)’),
    Details.value(‘Desc[1]‘,’VARCHAR(15)’),
    T.C.value(‘ConNo[1]‘,’VARCHAR(14)’),
    T.C.value(‘ConDate[1]‘,’VARCHAR(10)’),
    T.C.value(‘Add1[1]‘,’VARCHAR(50)’),
    T.C.value(‘Add2[1]‘,’VARCHAR(50)’),
    T.C.value(‘Add3[1]‘,’VARCHAR(50)’),
    T.C.value(‘CarrierName[1]‘,’VARCHAR(50)’)
    FROM @c.nodes(‘MyImportFile/Connote’) AS T(C)
    CROSS APPLY @c.nodes(‘MyImportFile/Connote/RecAddr/FreightLineDetails’) AS T2(Details)

    ———————————————————————————————-

    Could you please help.

  40. Sample file once again the sample file.

    27/06/2011
    123456789
    1234
    CCODE001
    CUSTOMER NAME
    N

    REC ADDR 1
    REC ADDR 2

    WORLD

    SENDER NAME

    SEND ADDR 1
    SEND ADDR 2
    SEND ADDR 3
    STATE
    POSTCODE

    S
    CARRIER NAME
    999123
    BY ROAD
    N

    6
    18
    0
    0
    0

    N

    10.00
    11.00
    0
    11.00
    11.00
    0
    11.00
    11.00
    1

    N

    N

    ABCDEFGHIJ000000990001
    ABCDEFGHIJ000000990002
    0

    T000123
    CUSTOMER
    6
    6

  41. Hi Mr. Pinal,
    How can i show the following xml in select view
    thanks
    sharon

    declare @xml xml
    set @xml =

    222
    223
    224

    result :
    SecurityLevelID ID
    222 25
    223 25
    224 25

  42. Hi Mr. Pinal,
    How can i show the following xml in select view
    thanks
    sharon

    declare @xml xml
    set @xml =
    User ID=25
    SecurityLevelID 222 SecurityLevelID
    SecurityLevelID 223 SecurityLevelID
    SecurityLevelID 224 SecurityLevelID
    UserID

    result :
    SecurityLevelID ID
    222 25
    223 25
    224 25

  43. So, where is the code to actually read XML from a *FILE* — The title says “example of reading xml file” but I see nothing about how to open and read data from an XML file on disk??

  44. Pingback: SQL SERVER – Methods for Accessing SQL Server XML Datatype – Quiz – Puzzle – 20 of 31 « SQL Server Journey with SQL Authority

  45. Hi Dave,

    I have a Stored procedure that reads XML file using the following query

    INSERT INTO inv_tempfile(BulkColumn)
    select BulkColumn from Openrowset(
    Bulk ”’ + @FileName + ”’, Single_Blob) as tt

    This query reads the entire content of the file into inv_tempfile. The BulkColumn is a VARCHAR(MAX) type.

    But the problem is, if the XML file contains “?” in any of its elements. Then this query is unable to read the whole file content.

    below is the sample XML Element file.

    #805 ? 2988 Alder Street, Vancouver tario C0B1C0

    Can u please explain me why this is happening and if there is any workaround.

  46. “XQuery [value()]: ‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *’”
    sir when i write the above command it shows me this error.

  47. I’m trying to pull a single int from an xml file stored on an app server into a stored proc, but I’m not sure how that would work even after reading all of the examples above. I’d need to reference the file as \\[app server name]\c$\[path]\[filename].xml. Also, the int in the xml file is not stored within a tag, but is instead a key. Here is an excerpt of the xml file up to the point of the int that I need to pull in (I want 22 to be returned to the stored proc):



  48. looks like the xml won’t post. trying again replacing with LT & GT:

    LT?xml version=”1.0″ encoding=”utf-8″ GT
    LTappSettingsGT
    LT!– COPY THIS FILE TO C:\DLR –GT
    LT!– Change the pieces to run to true –GT
    LT!– InputFeeds –GT
    LTadd key=”LoadPLPD” value = “false” /GT
    LT!– LoadPLPDCancelDays is used to calculate the cancel date and should be a value greater than zero/GT
    –>
    LTadd key=”LoadPLPDCancelDays” value = “22″ /GT

  49. Hi, how to read the xml in sql server 2005. Below my code is placed,

    declare @xml xml

    set @xml=

    01 first

    02 Second

    select a.b.value(‘.’, ‘nvarchar(50)’) as a_value
    from @xml.nodes(‘parent/child /subChild’) a(b)

  50. Hi pinal..
    I have a serious issues.. we are getting data’s daily in a XML format and we need to insert all these data’s into SQL SERVER. The problem is, we have a column called ‘Amount’ and same name we are maintaining in our SQL server.

    Sometimes, we are receiving XML files, with ‘Amount’ column name changed as ‘Amt’ and without checking if we load it, we are inserted with Null values.

    So I need to solution, that my table should accept AMOUNT as well as AMT as column name.

    Can you let me know the solution please.

    Thanks and regards,
    Dhinakaran

  51. Pingback: SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

  52. Vince
    Lakka
    Dinki
    Lucy
    Mac

    Hi Pinal / Members,

    I have a questin for you, how to read a file witohut using static mathod, the list is dynamic and it can be any numbers from 1 to 100s. I tried using static variables and its bit messy, there must be a proper way to read and convert this XML file to SQL

  53. Hello, would you explain me why do you use this symbols ‘ [ ', ' ] ‘ and why do you use the number 1 between them, please

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s