June 4, 2020 at 12:46 pm
All,
I'm trying to import XML using t-sql for the first time. I've created the following example to base my questions on:
declare @xml xml
select @xml='
<people>
<person id="1">
<name>Person1</name>
<address1>Address1</address1>
</person>
<person id="2">
<name>Person2</name>
<address1>Addressline1</address1>
</person>
</people>
'
SELECT
T.c.value ('id[0]','varchar(255)') AS id,
T.c.value ('name[1]','varchar(255)') AS name,
T.c.value ('address1[1]','varchar(255)') AS address1
FROM @xml.nodes('/people/person') T(c)
Firstly the above retrieves the child elements, name and address1, OK but I can't work out how to access the ID attribute? I realise that varchar isn't the best data type for the ID, I'm planning to change that later.
Secondly I've not seen an alias with a bracketed part before (I'm referring to T(C) ). I've tried searching to find out more about it but with no success. Is there a keyword that will help me search?
Lastly if I've approached importing XML, via t-sql, in the wrong way then any advice is welcome. I saw that it can also be done using openxml but I think using nodes is the more modern and preferred option?
Thanks
June 4, 2020 at 1:29 pm
I solved the first question as follow:
T.c.value ('id[0]','varchar(255)') AS id,needs to be:
T.c.value ('@id[1]','varchar(255)') AS id, Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply